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