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
HansVogelaar's avatar
HansVogelaar
MVP
Jun 29, 2022

nickcsy 

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.

 

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