Forum Discussion
Solved
Excel Formula for price by tier with an interval
Hi all
I'm hoping for an excel formula that can calculate the following tiers/sums based on the value in just one cell (A1) to show a result in only one other cell.
Where A1 is <25,000 price to pay is 50
value of 25,000 to 50,000 price is 80
50,001 to 100,000 price is 150
100,001 to 200,000 price is 300
200,001 to 300,000 price is 600
300,001 to 400,000 price is 1,500
400,001 to 500,000 price is 2,000
Then finally, for every 50,000 above 500,000, there is an additional 100 to pay.
I hope that makes sense, I'm quite at a loss on how to go about this.
Thanks a lot to anyone who even took the time to read this. Much appreciated.
NC
Sorry, my bad. See the attached version.
I'd create a lookup table for the first part (up to 500000). In the attached sample workbook, it is in I2:J8:
With a value in A2, the formula in B2 could be
=LOOKUP(A2,$I$2:$I$8,$J$2:$J$8)+QUOTIENT(MAX(A2-500000,0),50000)*100
This can be filled down if required.
Hi Hans!
Thank you so much for helping! That looks good.
I was was wondering if I could use something like this:
=IF(A1<25000,50,IF(A1<50000,80,IF(A1<100000,150,IF(A1<200000,300,IF(A1<300000,600,IF(A1<400000,1500,IF(A1<500000,2000, ... )))))))
This seems to take me up to the 500,000 point, but not the interval part beyond that.
Any ideas?
regards
NickYou can use that, then add the second part of the formula that I posted:
=IF(A1<25000,50,IF(A1<50000,80,IF(A1<100000,150,IF(A1<200000,300,IF(A1<300000,600,IF(A1<400000,1500,IF(A1<500000,2000)))))))+QUOTIENT(MAX(A1-5000000,0),50000)*100
