Compounding Numbers from Previous Row

kira11

Hello,

I have read through a few posts on referencing the above row and can't seem to get anywhere. I need to compound the % of the total revenue from the row above so eventually I will reach 100%:

Does anyone have any ideas on how to achieve this? Thanks!

Paul Newcome

Does this work for what you need?

=1 - (Amount@row / Total#)

kira11

I can't get that to work either. The formula I have in there works for this version but the sheet is on a Data Shuttle that rows will change slightly when imported and when a row gets deleted it gives an error instead of just referencing the new data that is in the hard coded row. @Paul Newcome

Paul Newcome

In my above formula, there is no hard coded row number. What exactly do you mean you can't get it to work? Are you getting an error message ro an unexpected output?

kira11

When I put in your suggestion it was just giving me the percentage of each row, but I need the percentage to be a cumulative based on the row above it. So the first row is 6% of the total and then row two is 2% of the total so the %Revenue cell for row 2 should be 8%, and so on. It works perfectly when I import the data and write the formula, but when I delete a row or the data shuttle runs I get an #UNPARSABLE starting at the row that was deleted or changed. Sorry if this does not make sense, I'm trying my best to explain.

Paul Newcome

Did you do the "one minus" at the beginning of my formula and then apply as a column formula? The below screenshot is basically what you are looking for, right?