Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning the total of two adjacent columns
Hi everyone, I am re-wording my original query to try an clarify the issue
and hope someone can help: Data is copied into a workbook starting with Row 7 Cell A7 In the example below: Col B Col C Col W Col X Col Y Row 7 1.30A 1 +80 Row 8 2 Row 9 3 Row 10 4 -20 Row 11 5 Row 12 6 -10 Row 13 7 +50 Row 14 2.00B 1 Row 15 2 +60 Row 16 3 Row 17 4 -20 Row 18 5 +40 Row 19 2.30A 1 Row 20 2 Row 21 3 -10 Row 22 4 -10 Row 23 2.45C Etc. Etc. What I am doing re the above example is as follows: Manually copy and paste the formula: SUM(W7:W13)+SUM(X7:X13) in cell Y13, one row above new entry in cell B14 SUM(W14:W18)+SUM(X14:X18) in cell Y18, one row above new entry in cell B19 SUM(W19:W22)+SUM(X19:X22) in cell Y19, one row above new entry in cell B23 Etc. Etc. The blocks of data can vary from 4 to 25+ rows and are randomly different. The "Total" cell with the formula in col Y is always one row above the new entry in col B. I hope this is clearer than my previous query and that someone can help me. I am trying to automate what is essentially a time consuming task. Thank you all in advance for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning the total of two adjacent columns
In Y7: =if(B8="","",SUM(W$7:X7)
In Y8: =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7), and copy the formula from Y8 down through the rest of column Y. Logically, this says that the first row's total is blank unless the next row has a new entry in column B, in which case the total is just the first row's sum. For subsequent rows, the total is blank unless the next row has a new entry in column B; in that case the total is the total from W and X in all rows, less the amounts already tallied in prior rows of column Y. "CAT" wrote: Hi everyone, I am re-wording my original query to try an clarify the issue and hope someone can help: Data is copied into a workbook starting with Row 7 Cell A7 In the example below: Col B Col C Col W Col X Col Y Row 7 1.30A 1 +80 Row 8 2 Row 9 3 Row 10 4 -20 Row 11 5 Row 12 6 -10 Row 13 7 +50 Row 14 2.00B 1 Row 15 2 +60 Row 16 3 Row 17 4 -20 Row 18 5 +40 Row 19 2.30A 1 Row 20 2 Row 21 3 -10 Row 22 4 -10 Row 23 2.45C Etc. Etc. What I am doing re the above example is as follows: Manually copy and paste the formula: SUM(W7:W13)+SUM(X7:X13) in cell Y13, one row above new entry in cell B14 SUM(W14:W18)+SUM(X14:X18) in cell Y18, one row above new entry in cell B19 SUM(W19:W22)+SUM(X19:X22) in cell Y19, one row above new entry in cell B23 Etc. Etc. The blocks of data can vary from 4 to 25+ rows and are randomly different. The "Total" cell with the formula in col Y is always one row above the new entry in col B. I hope this is clearer than my previous query and that someone can help me. I am trying to automate what is essentially a time consuming task. Thank you all in advance for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning the total of two adjacent columns
Hi Bpeltzer,
Just tested your formulae: Marvellous!! Thank you so much! It works right through except for the last one (row 268): but of course I don't have any more entry in the next row in col B so it won't return a total, but I can do this one manually; I can live with that, you save me an awfull lot of time, thank you again. Just one thing, at the end of formula one, Excell asked me to add a closing parentheses as in =if(B8="","",SUM(W$7:X7)) And the same again for formula two: as in =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7)) Hope I am not offending you in pointing this out; I'm a total newby re the writing of formulae so I wouldn't have known anyway. Have a great day "bpeltzer" wrote: In Y7: =if(B8="","",SUM(W$7:X7) In Y8: =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7), and copy the formula from Y8 down through the rest of column Y. Logically, this says that the first row's total is blank unless the next row has a new entry in column B, in which case the total is just the first row's sum. For subsequent rows, the total is blank unless the next row has a new entry in column B; in that case the total is the total from W and X in all rows, less the amounts already tallied in prior rows of column Y. "CAT" wrote: Hi everyone, I am re-wording my original query to try an clarify the issue and hope someone can help: Data is copied into a workbook starting with Row 7 Cell A7 In the example below: Col B Col C Col W Col X Col Y Row 7 1.30A 1 +80 Row 8 2 Row 9 3 Row 10 4 -20 Row 11 5 Row 12 6 -10 Row 13 7 +50 Row 14 2.00B 1 Row 15 2 +60 Row 16 3 Row 17 4 -20 Row 18 5 +40 Row 19 2.30A 1 Row 20 2 Row 21 3 -10 Row 22 4 -10 Row 23 2.45C Etc. Etc. What I am doing re the above example is as follows: Manually copy and paste the formula: SUM(W7:W13)+SUM(X7:X13) in cell Y13, one row above new entry in cell B14 SUM(W14:W18)+SUM(X14:X18) in cell Y18, one row above new entry in cell B19 SUM(W19:W22)+SUM(X19:X22) in cell Y19, one row above new entry in cell B23 Etc. Etc. The blocks of data can vary from 4 to 25+ rows and are randomly different. The "Total" cell with the formula in col Y is always one row above the new entry in col B. I hope this is clearer than my previous query and that someone can help me. I am trying to automate what is essentially a time consuming task. Thank you all in advance for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function returning the total of two adjacent columns
No offense taken; sorry for the omission. And thanks for the feedback.
--Bruce Just one thing, at the end of formula one, Excell asked me to add a closing parentheses as in =if(B8="","",SUM(W$7:X7)) And the same again for formula two: as in =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7)) Hope I am not offending you in pointing this out; I'm a total newby re the writing of formulae so I wouldn't have known anyway. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function returning SUM of 2 adjacent cols in 2 separate cells | Excel Worksheet Functions | |||
Returning adjacent values | Excel Discussion (Misc queries) | |||
Total based on text dependancy + number value in adjacent cell | Excel Discussion (Misc queries) | |||
Non Adjacent percent average total | Excel Discussion (Misc queries) | |||
add 3 non-adjacent % cells visually = 99.9, total shows 100.0 | Excel Worksheet Functions |