# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### 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
92
Replies
3
Views
137
Replies
7
Views
54
Replies
4
Views
122
Replies
7
Views
177

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

### Share this page ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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