Hi all, I've got a table that I'm referring to for a tiered pricing formula. I've read through as many threads as I can count but can't seem to find the answer to my issue. My formula is always a thousand off my manual calculation.
My formula is - =SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)
J2 = 430,970
Formula returns - 9,964.55
Manual calculation returns - 10,964,55 (100,000@0.035 (3,500) + 250,000@0.025 (6250) + 80,970@0.015 (1214.55))
Any ideas?
My table is as follows:
A B C D E
<tbody>
</tbody>
My formula is - =SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)
J2 = 430,970
Formula returns - 9,964.55
Manual calculation returns - 10,964,55 (100,000@0.035 (3,500) + 250,000@0.025 (6250) + 80,970@0.015 (1214.55))
Any ideas?
My table is as follows:
A B C D E
Min | Max | Threshold | Fee | Diff. Rate |
0 | 100,000 | 0 | 0.035 | 0.035 |
100,001 | 250,000 | 100,000 | 0.025 | -0.010 |
250,001 | 500,000 | 250,000 | 0.015 | -0.010 |
500,001 | 1,000,000 | 500,000 | 0.005 | -0.010 |
1,000,001 | 1,000,001 | 0 | -0.005 |
<tbody>
</tbody>