Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to skip a row in a formula that calculates totals?
I am using Excel 2003. I have 65 rows, and columns K-Q and S are manually
entered. Column R is the sum of K-Q. This is the formula that I have in column T except for the first row: =IF(R12*S12=0," ",((R12*S12)+T11)) I used this formula because the users do not wish for the running total to show in any but the last used row. Now they'd like to skip a row or two at intervals of their choice. My question is: How can I get my formula to continue to total if my users skip a row or two, without having to manually change my formula at each interval? Columns R and T are locked and protected. This is going to be their template. Thanks x10 to anyone who tries even if this is not possible. I just love this site!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to skip a row in a formula that calculates totals?
On Fri, 26 Dec 2008 14:19:00 -0800, ramona
wrote: I am using Excel 2003. I have 65 rows, and columns K-Q and S are manually entered. Column R is the sum of K-Q. This is the formula that I have in column T except for the first row: =IF(R12*S12=0," ",((R12*S12)+T11)) I used this formula because the users do not wish for the running total to show in any but the last used row. Now they'd like to skip a row or two at intervals of their choice. My question is: How can I get my formula to continue to total if my users skip a row or two, without having to manually change my formula at each interval? Columns R and T are locked and protected. This is going to be their template. Thanks x10 to anyone who tries even if this is not possible. I just love this site!! Try this formula in column T (row 12 as in your example) =IF(R12*S12=0," ",R12*S12+INDEX(T$1:T$1000,MAX(ROW(T$1:T11)*((T$1: T11)<" ")))) Note: this is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. i.e. you replace T11 in your formula with the INDEX expression. This expression returns the content of the cell in the T column with the highest row number upto 11, but omitting cells with the space in. This gives the possibilty to "skip row" when inputting the data and still have the correct summing. Note that you must change the 1000 to cover the number of rows that may contain input data. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to skip a row in a formula that calculates tota
Thank you, Lars-Ake.
I definitely appreciate your assistance. It took me a while to figure it out but I had a typo. It works beautifully!! Thank you SOOOOOOOOO much. I really to love this site!! "Lars-Ã…ke Aspelin" wrote: On Fri, 26 Dec 2008 14:19:00 -0800, ramona wrote: I am using Excel 2003. I have 65 rows, and columns K-Q and S are manually entered. Column R is the sum of K-Q. This is the formula that I have in column T except for the first row: =IF(R12*S12=0," ",((R12*S12)+T11)) I used this formula because the users do not wish for the running total to show in any but the last used row. Now they'd like to skip a row or two at intervals of their choice. My question is: How can I get my formula to continue to total if my users skip a row or two, without having to manually change my formula at each interval? Columns R and T are locked and protected. This is going to be their template. Thanks x10 to anyone who tries even if this is not possible. I just love this site!! Try this formula in column T (row 12 as in your example) =IF(R12*S12=0," ",R12*S12+INDEX(T$1:T$1000,MAX(ROW(T$1:T11)*((T$1: T11)<" ")))) Note: this is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. i.e. you replace T11 in your formula with the INDEX expression. This expression returns the content of the cell in the T column with the highest row number upto 11, but omitting cells with the space in. This gives the possibilty to "skip row" when inputting the data and still have the correct summing. Note that you must change the 1000 to cover the number of rows that may contain input data. Hope this helps / Lars-Ã…ke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula only calculates when clicked | Excel Worksheet Functions | |||
a formula which calculates empty cells | Excel Discussion (Misc queries) | |||
formula that calculates upon saving only | Excel Discussion (Misc queries) | |||
How do I write an Excel formula that re-calculates every 24 hrs? | Excel Discussion (Misc queries) | |||
Is there a template that calculates inventory totals? | Excel Worksheet Functions |