Forum Discussion

nickcsy's avatar
nickcsy
Copper Contributor
Jun 29, 2022
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...
excel
Formulas and Functions
nickcsy's avatar
nickcsy
Copper Contributor
Jun 29, 2022

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

HansVogelaar's avatar
HansVogelaar
MVP
Jun 29, 2022

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

  • nickcsy's avatar
    nickcsy
    Copper Contributor
    Jun 29, 2022

    HansVogelaar 

    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

Resources