Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
I have a numerous sets of data that varies from set to set in the amount of
rows each contains (one set will contain 2135 rows and one will have 3784 rows, etc.). Each set has 12 columns. I have some formulas set up that do various calcutions on the data. As things are now I am having to copy and drag the formula's down to match the end of the data. I also have to enter a formula that calucates the sum of one of the columns. This is very time-consuming. Is there a function/formula/option that will fill in my formula all the way to the end of the rows of data, no further? Is there a function/formula/option that will then calculate a the sum of a column of "x"-amount of rows? Assume the sum of column E. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
Hi,
Could you share with us the formula, to make appropriate suggestions .... Cheers Carim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
"Carim" wrote: Hi, Could you share with us the formula, to make appropriate suggestions .... Cheers Carim =ISNA(MATCH(D2,$P$2:$P$2500,FALSE)) is the formula that is in cell y2. =ISNA(MATCH(P2,$D$2:$D$2500,FALSE)) is the formula that is in cell z2. The formula checks to see if a sales receipt number from one list is on the other. I use both lists, one from the store and one from company database, to reconcile that month's sales. I sum column E which is the total sales for the month to see how far off the two reports are. When I find a sales receipt that needs to be added or subtracted to one report or the other the total sales sum will get closer. When they match I know that both lists contain the necessary sales receipts, I know that I am through reconciling. Some reports contain 3500 rows and others contain 5000+ rows. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
Hi,
I would suggest to look into two possibilities to pick the one which best suits your needs. 1. If the number of rows is different in each column, but known and therefore stored somewhere, I would recommend using OFFSET (Nbrows, 0) in your formulas 2. If the above is not possible, then I would use Define Name to locate the last cell and then use it in your formulas Hope this is clear enough Cheers Carim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
Oops ...
Missed the example for the formula : =ISNA(MATCH(P2,$D$2:Offset($D$2,2499,0),FALSE)) Carim |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a aariable amount or row
Oops ...
Missed the example with the formula : =ISNA(MATCH(P2,D2:OFFSET(D2,COUNT(D3:D65536),0),FA LSE)) Cheers Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating different percentages depending on amount | Excel Discussion (Misc queries) | |||
Calculating number of periods when payment amount changes | Excel Worksheet Functions | |||
Calculating total amount of sales for each salesman | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |