Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IFSum(Vlookup...
Need to divide a value in a column by a set of
running sum values in an adjacent column. Data looks like this: Product Month Bal RemBal Result Calculation: 100 01 $10.00 $10 100% 10/10 100 02 $5.00 $15 25% 5/(10+15) 100 03 $2.00 $12 32% 12/(10+15+12) ...... 100 60 $9.00 $9 .01% 9/(10+15+12+ 60 months of balances ) 400 01 $8.00 $8 100% 8/8 400 02 $5.00 $35 19% 5/(8+35) now we have a new value and the month's sequence starts at 1 (01). Currently the formula is: row 8 =P8/SUM(R$7:R8) row 9 =P9/SUM(R$7:R9) row 10 =P10/SUM(R$7:R10) row 11 =P11/SUM(R$7:R11) in this case, the new product started at row 7 and might continue for 60 more rows, or just 6 more, where the $R$7 part of the formula would change again. Usually, every 60 or so lines, (the # of months is different, depending on the product) l have to re-start the formula (replacing $R$7 with $R$67 for example) so the SUM starts with month 0 and continues summing the contents of column R , until the last month for that product. I have over 24,000 lines of this, it is getting very tedious to have to re-copy the formula every time there is a new product., making sure my formula captures a running sum starting with the first month and divides the value in the current row into that running sum. I'm planning on starting with "IF(P8<p7" - ie a new product has started so start the forumula using the current row as the cell placed in the formula "sum($R$7", start the running sum up, and divide the contents of the current row into the running sum values until a new product, where the process starts all over again. Is it possible to "Anchor" a running sum formula based on changing criteria with some kind of vlookup/Sumif (or something else)? |