Rounding To Nearest $0.95

[jlsCreative]

Hello all,

I don't know if this is even possible, but I have a cell that returns a certain value and sometimes it's very random. I need the cell to round to the nearest $0.95, but don't know if it's possible to do that. For example, a cell may return $39.64, but is there a way to force it to $39.95 without it going to $40.00? Any help would be appreciated.

Thanks,
Jason

 

[Vidar]

Hi Jason.

Try this one:
=ROUND(A1,0)-0.05

 

[Joe4]

=ROUND(A1,0)-0.05

That won't quite work for all entries. For example, 39.46 is closer to 39.95 than 38.95, but the formula will round it to 38.95.

You just need to modify your formula like this:

=ROUND(A1+0.05,0)-0.05

 

[Asala42]

This should do it:

=ROUND(A1+0.05,0)-0.05

[edit - bah I'm too slow]

 

[jlsCreative]

Thanks! I modified it to ROUNDUP, which narrows the error margin a little more to the number closer to where I need it.

 

[Joe4]

Thanks! I modified it to ROUNDUP, which narrows the error margin a little more to the number closer to where I need it.

Note that will always round up, never round down. So values like 39.01 would round to 39.95, not 38.95.
Are you sure that is really what you are intending to do? That is not what you asked for in your original post.

I need the cell to round to the nearest $0.95

 

[jlsCreative]

I just need it to be as close as possible and if need be I can make an adjustment. As I was testing the sheet, it returned closer numbers with the ROUNDUP than it did with ROUND. Not saying yours was incorrect, but ROUNDUP just helps better with this sheet in terms of the end number I need.

 

[Joe4]

I just need it to be as close as possible and if need be I can make an adjustment. As I was testing the sheet, it returned closer numbers with the ROUNDUP than it did with ROUND. Not saying yours was incorrect, but ROUNDUP just helps better with this sheet in terms of the end number I need.

That doesn't make sense based on your original request (unless you didn't really present a clear picture of what you were trying to do).
If you look at my last post, I showed an example where ROUNDUP will produce the wrong results.

Can you post an example to show us what you are talking about, where ROUNDUP works better than the ROUND functions we provided?

 

[jlsCreative]

I'm working with a publishing company and they want $12.32 to round up to $12.95. ROUND would push it down to $11.95, as I just tested. I apologize if my original post conflicts with the final result, I am fairly new to Excel and just wanted to know how to round up and subtract five cents.

 

[Joe4]

OK. That's makes sense. It was the word "nearest" that threw us for a loop. "Nearest" implies that you can go down, but you always want it to only go up.