Function help

[Deanna_Wilson]

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 500000, 0.08 plus 0.01 per each additional 100000. For example of cell X was 800000, the formula/function result will be 0.11(0.08 + 0.03). I'd want it to round up so for 850000, it'd be 0.12. 

[#2]

@Deanna_Wilson 

Hi Deanna, this formula do the Job. Your value is in A1:

=0.08+(ROUNDUP(A1-500000,-5)/10000000)

[#3]

@Deanna_Wilson 

With your amount in A1:

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

[#4]

@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.)

[#5]

@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)