rounding rules ROUND vs ROUNDUP

[GShen]

I am doing a calculation on a report which needs rounding.
I am using the ROUND verb but it takes .5 and rounds down.
I looked up and saw 2 verbs besides ROUND which are ROUNDUP and ROUNDDOWN. I figured ROUNDUP would work. I then get the #name? after it. This is what HELP has to say:

ROUNDUP
Rounds a number up, away from 0 (zero).

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Does this need to be installed or am I missing something with the rounding rules? I couldn't find anywhere where it states the rules for rounding. Math 101 always said to round up for 5 or better.

Also, when coding under the control source, is that considered VBA or something else? When you code an EVENT it is done using VBA, not sure what it is called under CONTROL SOURCE coding.

Thanks,

 

[xlbo]

Go to Tools>References and make sure that the Analysis Toolpak is ticked. If not, tick it. I think I'm right in saying that ROUNDUP is not available in vanilla excel - only with analysis toolpak installed (don't worry it doesn't cost anything and should be there waiting for you to tick the reference) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GShen]

finally got back to this. Found out Roundup is not something I wanted either. It rounds up all the time. I ended up added .5 or .05 etc. to the field before I rounded.
It works. If I needed 3 dec. places I had to add .0005, etc., so I would not change the actual value. I can't believe there isn't something out there to do this.
Thanks,

 

[Zathras]

The function you are looking for is

 CEILING()

 

[GShen]

I must be losing my mind because I have used this before. Will use next time. Not about to redo something which is working.
Thanks for reminding me! Don't know how I missed this.
On to bigger and better stuff.

 

[Loomah]

hi
I'm a little confused by this thread!! OK no change there but for rounding the ROUND function should do the trick. The answer may be dependant on what you are dealing with.

For instance if you want all calculations rounded to 2 decimal places then =ROUND(A1,2) will do the trick for the value in A1. XL will look at the third decimal place. If that is >=5 then it will round up the second decimal, otherwise it will be left unchanged.

EG 1.2549999999 will become 1.25 and 1.25500000 will become 1.26

ROUNDUP & ROUNDDOWN will do what the names suggest.

If this is of no use it may help if you post an example of what you might want to round and what you expect the result to be, ie how many places you want a figure rounded to.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[GShen]

Loomah,
Thanks for replying but I have this working. It is not for Excel, it is in the VB code using ACCESS. Round does NOT round 1.25 as 1.3, it makes it 1.2. I was doing some funky stuff to add .005 to all calcs. Therefor 1.25 would be 1.255 and round it to 1.3. From what Zathras pointed out, I could use the Ceiling() function to do this.
Hopefully, that removes the confusion.

 

[Loomah]

that's ok
something happened here and the zathras post wasn't there when i set out on my epic!
didn't even realise the analysis toolpak was available for access - but then i don't really use access!!



If a man says something and there are no women there to hear him, is he still wrong?