Forum Discussion

Deanna_Wilson's avatar
Deanna_Wilson
Copper Contributor
Oct 12, 2022

Function help

Hello,    I apologize if this is fairly beginner but I'm not finding an answer by searching. How do I do the following?    If cell X is less than or equal to 500000, 0.08  , if greater than 5000...
excel
Formulas and Functions
SnowMan55's avatar
SnowMan55
Bronze Contributor
Oct 14, 2022

Deanna_Wilson 

As you have not identified either prior reply as correct...

I have to wonder if by "round up" you meant "round" (the colloquial sense).  E.g., should 840000 (like 850000) round up to 900000?

 

If you were to say "no, it should round down to 800000", my formula would be:

=IF( A1 <= 500000, 0.08, 0.08+0.01*ROUND( ((A1-500000)/100000), 0 ) )

See the attached spreadsheet for a comparison of the formula results; you can experiment by creating rows with your own formulas.  (If your formulas reference the cells containing the "X value" by using the column letter followed by "$1" rather than by "1", it's easy to copy a row with existing formulas down.)

 

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Oct 14, 2022

    SnowMan55  wrote:  ``wonder if by "round up" you meant "round" ``

     

    If so, I think the simplest change is to replace CEILING with MROUND, to wit:

     

    =0.08 + 0.01*MAX(0, (MROUND(A1, 100000) - 500000) / 100000)

     

    -----

    PS....  Looking at the Excel that SnowMan55  attached, he has an example with negative values.  If that might be a legitimate possibility, change the MROUND formula as follows to avoid a #NUM error:

     

    =0.08 + 0.01*MAX(0, (MROUND(B1, SIGN(B1)*100000) - 500000) / 100000)

     

     

Resources