Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula until condition met
How can I SUMIF subject to each successive previous cell's value being less
than the value of the former cell? I'm using a COUNTIF formula to determine the number of entries in each column. The table looks something like this: V2A: 10 20 5 10 5 10 where "V2A" stands for "Value to Add." CI: 2 6 4 1 3 5 where "CI:" stands for "COUNT IF" values. R1: x x x x x x where "R" stands for "ROW" as in "ROW 1:" R2: x x x - x x where "x" stands for a value in a cell. R3: - x x - x x where "-" stands for an empty cell. R4: - x x - - x R5: - x - - - x R6: - x - - - - Res: 0 10 0 0 10 20 where "Res:" stands for "Results." Explanation: I am looking for a formula that will calculate the results row, (the "Res:" row). I have 31 columns, so nested IF statements probably won't work. I used COUNTIF to determine the values to the right of CI. The formula that I need must SUM the V2A values if the CI values to the columns in coluleft are lower thanmns to the right. Therefore, the result in column 1 is '0' because there is no value to the left of the column. The result in column 2 is 10, because its CI value of 6 is greater than the value of 2 from the column to its left. The results of columns 3 and 4 are 0 because the columns to their left are greater. Notice that since the 2nd column has a greater value than those to its left, (column 1), column 1's value is not SUMmed in formulas to the right of column 2. The result in column 5 is 10, because the row to the left has a lesser CI value. The result for column 6 is 20, because it is greater than the previous 3 columns CI values, but less than column 2's CI value. This problem has been driving me nuts! Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition Formula | Excel Worksheet Functions | |||
Add Condition to Formula | Excel Discussion (Misc queries) | |||
Formula For If & Or Condition | Excel Discussion (Misc queries) | |||
HELP? ...a second condition in one formula | Excel Worksheet Functions | |||
Add condition to formula | Excel Worksheet Functions |