How to use Ceiling to round?

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Can anyone help me with a rounding issue

I have a formula that looks like this

F7*F27
16,995*.92134 = 15,658.17

I then need to Round to the nearest 500 and then subtract 5

15,659 rounded to the neares is 15,500 then subtract 5 = 15,495
 
You can use MRound from the analysis toolpack which rounds to a multiple but
if you send it to someone who does not have the toolpack installed the it
turns into a #value Error. That being the case I prefer...

=round((F7*F27)/500, 0) * 500
 
Make sure the Analysis Tool-Pak Add-in is activated. (Tools - Add-ins).
Formula will be something like:

=MROUND(A2,500)-5
 
what's strange is that all of these work in some instances but not all

What about
16,995*125.6325 = 2,135,124
Round to the nearest and minus 5
= 2,092,995
but it's giving me 2,134,995
 
2,135,124 rounded to the nearest 500 is
2,135,000
subtract 5 and you get
2,134,995

Where are you getting
2,092,995
 
the formula worked for 1 instance but not others and I'm not sure why??? I
tried this one and the Round and the Floor and I can't seem to get any of
them to work for this instance:

F7*F28 = 2,135,124
Round to the nearest 500 and subtract 5 = 2,092,995

but MRound(F7*F28,500)-5 = 2,134,995
 
you have a good point .. in another workbook we have the person has used
ceiling in their formula but it's looking at something a little different and
our numbers are supposed to be able to tie out?

theirs says:

= Ceiling(E8*G86,500)-5

1255697*(1+(2/3),500)-5 = 2,092,995
 
Is their number correct??? Just becuase that is what they have does not mean
that it is correct. Assuming that it is correct I do not quite follow what
you have. Give me the values of the source cells to be calculated and the
formula...
 
As Jim pointed out, 2,135,124 rounded to the nearest 50 is 2,135,000.

You'll need to show us the complete formula/data that's giving 2,092,995 for
us to figure out what the discrepency is.
 
Back
Top