Advanced rounding question?

[naclu]

Hi,

I've searched some of the other threads, and I find something that is similar to what I need, but not exactly. I hope that one of the volunteers can help, and appreciate the effort in advance.

I work in retail and I'm trying to create a formula that will take a dollar amount and round it up or down, based on if the numbers after the decimal AND THEN make it end in .95.

For example, if the value is 12.33, then I need to make it 11.95. If the value is 12.78, then I need to make it 12.95.

In human terminology, I'm guessing that the formula needs to first check the numbers after the decimal. IF the numbers are < 50, then perhaps round down and then subtract .05. If the numbers are =/> 50, then round up and then subtract .05.

I understand the concept, but don't know how to implement it.

Thanks again in advance...
Nancy

 

[shg]

Maybe =round(a1, 0) - 0.05

 

[VoG]

Try

=IF(MOD(A2,1)>=0.5,INT(A2)+0.95,INT(A2)-1+0.95)

 

[naclu]

Both of those formulas worked.... The simplicity of yours, SHG, made me feel so stupid! LOL...

Thank you both so much!

 

[VoG]

The simplicity of yours, SHG, made me feel so stupid! LOL...

You and me both

 

[shg]

Peter's offers more flexibility if you wanted to break someplace other than at 0.5, though.