SOLVED

Excel Formula for price by tier with an interval

Copper Contributor

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

5 Replies

@nickcsy 

I'd create a lookup table for the first part (up to 500000). In the attached sample workbook, it is in I2:J8:

S1526.png

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
Nick

@nickcsy 

You 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

@Hans Vogelaar 

OK cool. 
Just gave it a test.  I'm afraid when A1 is 500,000 or above, I get only get "-" value unfortunately.

Will keep trying to tweak it in the meantime.

Thanks again.

Nick

best response confirmed by nickcsy (Copper Contributor)
Solution

@nickcsy 

Sorry, my bad. See the attached version.

1 best response

Accepted Solutions
best response confirmed by nickcsy (Copper Contributor)
Solution

@nickcsy 

Sorry, my bad. See the attached version.

View solution in original post