Replies

J:

Some work arounds with Excel.

A common alternative is to have all sums at the top of the data not at the bottom, then when you insert a rows the total row is on top.

If you are only going to insert a few rows the following may get you there. I would begin by having adequate top and bottom margins for the worksheet. Next insert a page break just below the Total Lines. Just make sure you have a place in the workbook to make note of how many rows are currently printed. then compare that with the amount when new rows are inserted.

When you insert rows you either change the top and bottom margins a sufficient amount to have all totals back at the bottom, or use the Page Setup Scaling to reduce the size of print out from say 100 % to 90% or a combination fo both. Print preview the results until its what you want. A better version of the above is find out the height of the rows (hopefully they are all the same). Since ther are 72 Point to an inch you could set up a math formula in Excel that will quickly solve for the proper adjustments that you would then put in Print Setup for the amount of additional rows you inserted.

Good Luck

Tom Duthie

HANS:

You did warn to avoid circulars, looks like to other software failed to catch these. I will use Excel to do some data cleaning and start again.

Thanks,

Tom Duthie

Hans:

Thanks for your assistance and warnings.

The actual data is a download from other software and has been checked to ensure all ownership is 100%. In addition, by query the other software already confirmed there are no circular ownerships . Too bad the blasted other software won’t do the tiered ownership computatons.

Based on your observations, I fully agree that Access is the best approach for this project.

If I run into further problems on this one I will post them to the Access Forum

Regards,

Tom Duthie

A workaround.

Using string formulas find out the Length (Function Len) of each cell.
For those over 256 use string formula Mid to break up too long cells
By example

1. In a new empty cell put the formula =Mid(A1,1,255) where A1 references the cell that is too long
2. In a second new cell Mid(A1.256.511)
Continue until all text has been cut down
Then using copy then paste special turn the new cells from formulas to Values.
Latly delete the too long Text.
Now you can copy the Sheet to anywhere.
If you have a lot of work consider a Macro to repeat this task

Tom Duthie

Rushatiindia:
After review I beleive your problem is one of decimal places. The acutal annual rate for 7.50% compounded quarterly is not .0771 rather it is .0771358568. When you use this in the formula
FV(.0771358568/12,60,10,1) The result is 730.78. To have an exact match to the XIRR computation you would need to use the same decimal percision in both formulas. What that percision is would be up to the user.

I think the same would be true to you second question.

Regards,

Tom Duthie

Dear Rushatiindia:

Welcome to the Lounge.

I think I have an answer to Problem 1

=FV(0.075/4,60,10,,0) which 1,092.43
Less Amounts Invested 600 gives net return of 492.43
I check the result using a book of compound and annuities tables.

As to problem 2, I leave that to someone else in the Lounge.

Tom Duthie

See the below from Excel VBA:

FieldInfo Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date

xlDMYFormat DMY date

xlYMDFormat YMD date

xlMYDFormat MYD date

xlDYMFormat DYM date

xlYDMFormat YDM date

xlEMDFormat EMD date

xlSkipColumn Skip Column

You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used.

The column specifiers can be in any order. If there’s no column specifier for a particular column in the input data, the column is parsed with the General setting.

Notes

If you specify that a column is to be skipped, you must explicitly state the type for all remaining columns or the data will not parse correctly.
If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the above date formats for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General.
This example causes the third column to be parsed as MDY (for example, 01/10/1970), the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 3), Array(1, 2))

If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.

Good Luck.

Tom Duthie

Try this two liner
Columns(1).Select
imax = Worksheetfunction.Max(Selection)

Tom Duthie

The question you brought up is a common one. The solutions listed below are very good.
Just as an alternative I have attached a sample workbook that is often used at my work place and should benefit you and other users
The workbook attached has 3 Worksheets. The first is the Cover Sheet which sole purpose is to help keep you workbook better organized. The Tab names are Hyperlinks to the actual worksheets. It is currently protected. You can unprotect it and make any modifications you desire, but it should then be protected so users cannot delete, move or change the name of this sheet. Also in the attached workbook the “Last saved by” row does not update. If you want this option please use the VBA Editor and uncomment the VBA line Worksheets.Item(1).Range(“C18”).Value = ActiveWorkbook.UserStatus wherever it occurs.
If you change the Tab names or create new Tabs you must resave the workbook for the Cover Sheet to update.
Perhaps this may help. I also hope it can help others better organize their large workbooks.

Regards,

Tom Duthie

You are getting a different answer because you are working with two different numbers. expand the columns C and D and format the cells to show say 10 decimal places.

To get the same answer you should use the Round function. In the case of your formula the top one should be =Round(A1/B1,3).

Regards,

Tom Duthie

The way that would be done without VBA would be to first copy the new values on the Top Active Sheet of your Group. Then Select the range of new values on the active sheet. Lastly, Choose Edit Fill Across Worksheets. When the dialog box opens selects All.

To see the code just record a Macro doing the above and then modify lthe code to fit within your Macro.

Good Luck

Tom Duthie

You can also try

Format/Cells/Special/Phone Number.

This is available on the 2003 English version of Excel that I have installed.

Regards,

Tom Duthie

Brad:

Simple way without Macro would be
1. go to Cell A1
2. From the Edit menu choose the Go To command and click the Special Button
3. In the Go To Special dialog box select Current Region and click OK
4. For a second time from the Edit menu choose the Go To command and click the Special Button
5. In the Go To Special dialog box select Blanks and click OK
6. Excel will select all blank cells in the region and make one cell the active cell
7. In the active cell type = and press the right arrow key (Excel understands this to mean for blank cells get the data in the cell right of it
8. Hold Down the CTR key and press Enter. This will fill all the blank cells in Col A with the Values in Col B

Good luck

Tom Duthie

Try the code below I think this does what you want
Regards Tom Duthie

Dim MyCount As Single
Dim NextNo As Single
Dim ColsToSize As Single
Dim r As Single

Selection.Columns(1).ColumnWidth = 25
NextNo = 2

MyCount = Selection.Columns.Count
ColsToSize = MyCount – 1

If MyCount < 6 Then
For r = NextNo To MyCount
Selection.Columns(NextNo).ColumnWidth = 15 / ColsToSize
NextNo = NextNo + 1
Next r

Else

For r = NextNo To MyCount
Selection.Columns(NextNo).ColumnWidth = 55 / ColsToSize
NextNo = NextNo + 1
Next r

End If

Not 100 % sure, but I believe its some type of template.

Regards,

Tom Duthie