Interval pricing of volumes using IF formula

oops think i may have put this in the wrong section... mods please move or delete

Hi Fotis, thanks for that. The problem is the numbers are are cummulative for example at June there are 105,000 units sold year to date therefore the first 80,000 units will be priced at £3 and the difference of 25,000 units priced at £4 if that makes sense, this is what makes the formula quite tricky

sorry about that.

Your IF formula only works if unit sales were non cummulative.

As per the example, unit sales in May are 19,000. This means a total of 85,000 units have been sold from the beginning of the year (January - May). The first 80,000 sales will be priced at £3 and 5,000 will be priced at the next interval of £4.

Therefore of the 19,000 unit sales in May the cost would be £62,000 [(14,000 units x £3)+(5,000 units x £4)]

does this help at all?

Slightly reduced. if you don't care about the 400000 unit limit (i.e. anything>240000 is £6).

=SUMPRODUCT((A1-{0,80000,120000,240000})*(A1>{0,80000,120000,240000}),{3,1,1,1})

Pauley

I read the follow up notes. To handle this monthly:
Put this in cell C2
=SUMPRODUCT((B2-{0,80000,120000,240000,400000})*(B2>{0,80000,120000,240000,400000}),{3,1,1,1,0})

Put this in cell C3
=SUMPRODUCT((SUM($B$2:B3)-{0,80000,120000,240000,400000})*(SUM($B$2:B3)>{0,80000,120000,240000,400000}),{3,1,1,1,0})-SUM($C$2:C2)
and then drag down to cell C13.

Pauley

thanks for all the help guys, wow Pauley that formula is insane! <3