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

#### ccgrant11

##### New Member
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:

 Row/Column I J K L M N 1 (Price Break Point) 2 2000 5000 10000 15000 25000 50000 3 \$17 \$15 \$13 \$10 \$5 \$2

<tbody>
</tbody>

=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!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Oaktree

##### MrExcel MVP
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

##### New Member
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:
 J1 K1 L1 M1 N1 O1 P1 J2 0 2000 5000 10000 15000 25000 J3 \$17 \$15 \$13 \$10 \$5 \$2

<tbody>
</tbody>

Incremental Calculation Matrix:

 A27 B27 C27 D27 E27 F27 G27 H27 A28 Users/Transactions <> 1 2 3 4 5 A29 1,000 <> A30 1,500 <> A31 2,000 <> A32 2,500 <> A33 3,000 <>

<tbody>
</tbody>

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

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

Replies
0
Views
84
Replies
2
Views
115
Replies
2
Views
571
Replies
4
Views
301
Replies
0
Views
205

### Forum statistics

1,171,628
Messages
5,876,524
Members
433,199
Latest member
guerin47 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back