Hello all,
I’d need some help with a formula related to calculating a commission based on dollars transacted.
For the sake of the argument, there are three tiers of transacted dollars:
Tier 1: First $10M
Tier 2 : $10M-$25M
Tier 3: > $25M
Commission is % based. The % value is the sum of two % values in non-contiguous cells (B2 and B4 for tier 1, B6 and B8 for Tier 2, B10 and B12 for Tier 3).
Unfortunately this is part of a large cost model and it is not possible to sum them up and have the total for each tier in one cell.
Assuming the total transacted is in A1, I tried this formula to calculate the commission:
=SUMPRODUCT(--((A1)>{0,10000000,25000000}),--((A1)-{0,10000000,25000000}),(B2+B4, B6+B8-(B2+B4), B10+B12-(B6+B8))
but it doesn’t seem to work, as the third array is not recognized as such.
Any idea on how to make it work?
Thank you in advance for your help!!
Bookmarks