Formatting of multiple nested IF(AND... statements help

[ccgrant11]

I am attempting to deliver a pricing model (matrix). The model is dependent on the number of average transactions (x-axis) and the number of overall customer. As pricing increases, the incremental cost decreases (i.e. the incremental cost at 5,000 is less than that at 1,000).

I have created the following table to support the below equation:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1 (Price Break Point)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2000[/TD]
[TD]5000[/TD]
[TD]10000[/TD]
[TD]15000[/TD]
[TD]25000[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$17[/TD]
[TD]$15[/TD]
[TD]$13[/TD]
[TD]$10[/TD]
[TD]$5[/TD]
[TD]$2[/TD]
[/TR]
</tbody>[/TABLE]


=IF(AND((B28*C27>0),(B28*C27<=I2)),(B28*C27*I3),IF(AND((C27*B28>I2),(C27*B28<=J2)),((I2*I3)+(C27*B28-I2)*J3),IF(AND((C27*B28>J2),(C27*B28<=K2)),((I2*I3)+((J2-I2)*J3)+((C27*B28-J2)*K3)),IF(AND((C27*B28>K2),(C27*B28<=L2)),((I2*I3)+((J2-I2)*J3)+((K2-J2)*K3)+((C27*B28-K2)*L3)),IF(AND((C27*B28>L2),(C27*B28<=M2)),((I2*I3)+((J2-I2)*J3)+((K2-J2)*K3)+((L2-K2)*L3)+((C27*B28-L2)*M3)),IF(AND((C27*B28>M2),(C27*B28<=N2)),((I2*I3)+((J2-I2)*J3)+((K2-J2)*K3)+((L2-K2)*L3)+((M2-L2)*M3)+((C27*B28-M2)*N3))))))),((I2*I3)+((J2-I2)*J3)+((K2-J2)*K3)+((L2-K2)*L3)+((M2-L2)*M3)+((N2-M2)*N3)))

Messy, I know, and I am getting an error in response: "you've entered too many arguments for this function". 'B28' and 'C27' represents a specific user count and transaction count ('B28' * 'C27' = 1,000).

Is there something wrong with my formatting - or perhaps the way I am leveraging the nested IF(AND... statements. Thanks for the help in advance!

 

[Oaktree]

Welcome to the board.

So, the first 2000 units have a price of $17, the 2001 - 5000th units each have a price of $15 and so forth? Kind of like a tax table...

Change your row 2's values to be 0, 2000, 5000, 10000, 15000, and 25000 so they're the low end of the range instead of the high end of the range.

Then use =SUMPRODUCT(I4:N4-$H$4:$M$4,(B28*C27)-I2:N2,N((B28*C27)>I2:N2))

See the SUMPRODUCT section of this write-up for an explanation of what it's doing: Income Tax Formula | Excel University

 

[ccgrant11]

Thanks for the quick and simplified response, Oaktree. This seems to be a great solution for the problem I initially posed - however, I am having some issues in implementing the SUMPRODUCT formula into my spreadsheet.

Based on your recommendation, and some investigation into the SUMPRODUCT formula, I have entered the following in the pricing break point table:

Price Break Point Table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]J1[/TD]
[TD]K1[/TD]
[TD]L1[/TD]
[TD]M1[/TD]
[TD]N1[/TD]
[TD]O1[/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]0[/TD]
[TD]2000[/TD]
[TD]5000[/TD]
[TD]10000[/TD]
[TD]15000[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]$17[/TD]
[TD]$15[/TD]
[TD]$13[/TD]
[TD]$10[/TD]
[TD]$5[/TD]
[TD]$2[/TD]
[/TR]
</tbody>[/TABLE]


Incremental Calculation Matrix:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A27[/TD]
[TD]B27[/TD]
[TD]C27[/TD]
[TD]D27[/TD]
[TD]E27[/TD]
[TD]F27[/TD]
[TD]G27[/TD]
[TD]H27[/TD]
[/TR]
[TR]
[TD]A28[/TD]
[TD]Users/Transactions[/TD]
[TD]<<blank>>[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A29[/TD]
[TD]1,000[/TD]
[TD]<<blank>>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A30[/TD]
[TD]1,500[/TD]
[TD]<<blank>>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A31[/TD]
[TD]2,000[/TD]
[TD]<<blank>>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]2,500[/TD]
[TD]<<blank>>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A33[/TD]
[TD]3,000[/TD]
[TD]<<blank>>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I have entered the following SUMPRODUCT formula in cell D29 - the formula currently returns "$0":

=SUMPRODUCT(D29:O29-$C$29:$N$29,(D29*B29)-K2:P2,N((D28*B29)>K2:P2))

I would seem that the formula is not accounting for the incremental cost differences in K3:P3.

Thanks again for your assistance with this!

 

[Oaktree]

I think you want D29=SUMPRODUCT(K3:P3-J3:O3,(D28*B29)-K2:P2,N((D28*B29)>K2:P2))