How to use Ceiling to round?

[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

 

[Jim Thomlinson]

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

 

[Jacob Skaria]

Try
=FLOOR(F7*F27,500)-5

If this post helps click Yes

 

[Luke M]

Make sure the Analysis Tool-Pak Add-in is activated. (Tools - Add-ins).
Formula will be something like:

=MROUND(A2,500)-5

 

[Jim Thomlinson]

Sorry forgot to subtact 5...

=round((F7*F27)/500, 0) * 500 - 5

 

[Heather]

Thank you everyone!!! This was a huge help!

Try
=FLOOR(F7*F27,500)-5

If this post helps click Yes

 

[Heather]

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

 

[Jim Thomlinson]

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

 

[Heather]

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

 

[Heather]

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

 

[Jim Thomlinson]

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

 

[Luke M]

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.