Glenn wrote:
jaime_ottawa wrote:
I'm trying to model a pricing structure based on volume tiers, but
there are too many embedded if statements and the function won't work.
It's a 'step-pricing model' meaning that the first 12000 transactions
are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37
each, then the next 30,000 up to 60,000 are 0.35 and so on (see
below).

How can I determine revenue from this pricing scenario?

I had originally used embedded 'if' statements but my Excel 2003 won't
allow me more than 7. Is there any other way to do this?

12,000 0.39
30,000 0.37
60,000 0.35
120,000 0.33
180000 0.31
240000 0.29
300000 0.27
360000 0.25
480000 0.23
600000 0.21
720000 0.19
900000 0.17
1200000 0.15
1800000 0.13
2400000 0.11
3000000 0.09

Assuming I understand your needs properly and your table above is in
A2:B17 and A1 is blank, put the number of transactions in D2 and this in
E2:

=SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17))
+(D2-INDEX(A2:A17,MATCH(D2,A2:A17,1)))*
INDEX(B2:B17,MATCH(D2,A2:A17,1)+1)


Correction...A1 should be zero and E2 is as follows:

=SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17))+
(D2-INDEX(A1:A17,MATCH(D2,A1:A17,1)))*
INDEX(B2:B17,MATCH(D2,A1:A17,1))