Cumulative sum formula

**Africa** · 02-07-2012, 11:16 AM

Hi

I need to make a cumulative calculation using the following figures.

Number of transactions
52
71
34
87
96

For the first 100 transactions I will be charged $300, from 101 - 200 it will be $275, and for 201+ it will be $250.

I need to populate the figure in each line so I can subtotal at the end.

Any help would be great.

**Andrew-R** · 02-07-2012, 11:27 AM

With your transactions in column A starting in row 2 this formula in column B, starting at row 2, should work:

=(MIN(30000,SUM(A$2:A2)*300)+MAX(0,MIN(27500,(SUM(A$2:A2)-100)*275))+MAX(0,(SUM(A$2:A2)-200)*250))-SUM(B$1:B1)

**zbor** · 02-07-2012, 11:32 AM

Maybe this:

**Andrew-R** · 02-07-2012, 11:39 AM

Your formula gives different results to mine.

I don't want to make judgements, but mine are more correct that yours

**zbor** · 02-07-2012, 11:41 AM

So the main question is: what's expected result for Input 101

**Andrew-R** · 02-07-2012, 11:44 AM

$30,275 - 100 transactions @ $300 + 1 transaction @ $275

However, if they were laid out as two transactions:

100
1

Your formula would correctly calculate the first transactional value at £30,000, but then would put the second at $30,275, because it would add the value of the 1st 100 transactions to the 2nd single transaction.

**Marcol** · 02-07-2012, 11:44 AM

I must be reading this differently, see this workbook.

**zbor** · 02-07-2012, 11:49 AM

Originally Posted by Andrew-R

Your formula would correctly calculate the first transactional value at £30,000, but then would put the second at $30,275, because it would add the value of the 1st 100 transactions to the 2nd single transaction.

And that's how I understand cumulative SUM...
But also we have now Marcol solution that also have sence...

Now we only need to wait and see what user expect as result.

**Andrew-R** · 02-07-2012, 12:01 PM

Place bets now!

**Africa** · 02-08-2012, 05:42 AM

Hi

Apologies for not responding yesterday, I got dragged into meetings all day. I got the correct values from Andrew R's initial response, thanks to all for your help and my apologies if my explanation wasn't clear. I guess the word cumulative confused the issue.

Thanks again.