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

ccgrant11

New Member
Joined
Dec 2, 2016
Messages
2
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/ColumnIJKLMN
1 (Price Break Point)
22000500010000150002500050000
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
Joined
Jun 20, 2002
Messages
8,004
Office Version
  1. 365
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
Joined
Dec 2, 2016
Messages
2
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:
J1K1L1M1N1O1P1
J2020005000100001500025000
J3$17$15$13$10$5$2

<tbody>
</tbody>


Incremental Calculation Matrix:

A27B27C27D27E27F27G27H27
A28Users/Transactions<<blank>>12345
A291,000<<blank>>
A301,500<<blank>>
A312,000<<blank>>
A322,500<<blank>>
A333,000<<blank>>

<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
Joined
Jun 20, 2002
Messages
8,004
Office Version
  1. 365
I think you want D29=SUMPRODUCT(K3:P3-J3:O3,(D28*B29)-K2:P2,N((D28*B29)>K2:P2))
 

Watch MrExcel Video

Forum statistics

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

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
Top