Excel number format 4

[srogers]

In Lotus 1-2-3 the number 32.8650 displays as 32.86 when I format it to two decimal places. But in Excel that same number is displaying as 32.87.

Is that controlled by some setting I can change in Excel or is it just that the two applications round differently?

Thank you.

 

[bazzert]

As far as I remember the convention for rounding is 0-4 down 5-9 up so I'd suggest that Excel is correct and that what you had in Lotus was just displaying the 2 decimal places with no rounding

 

[schat]

hi srogers
i agree with bazzert - excel's got it right. not aware of any settings you can change, but if it's really bugging you and you want to display 32.8650 as 32.86 you can use the rounddown function. the syntax is =ROUNDDOWN(A1,2) where A1 contains the value and the '2' tells the formula how many decimal places to round down to.
hth
schat

 

[xlbo]

What excel is doing is DIPLAYING the number to 2 decimal places which involves rounding the 3rd decimal place up or down (if there is one). If you look in the formula bar, the full number will still be there and any calculations will be based on the FULL number. As per the other 2 posts, excel is correct in doing this <5 gets rounded down >=5 gets rounded up

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[srogers]

Ok, thank you. I don't have a problem with it but I'm anticipating questions from those whom I'm doing the conversion for and I just wanted to be sure of what was going on before that happens.
Thanks so much.

 

[TonyJollans]

Hi srogers,

A slight further note on this. There is no right and wrong here, just different types of rounding.

Excel always rounds .5 up, so 32.865 rounds to 32.87 as you observe.

VBA, and Access, and presumably 1-2-3, round .5 towards the even number. This type of rounding is often called 'Bankers Rounding' but has several other names and is designed to minimise collective errors when lots of rounded halfs are added up. With this type of rounding 32.8[red]6[/red]5 rounds down to 32.86, but 32.8[red]7[/red]5 rounds up to 32.88.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[srogers]

Thanks Tony -
So VBA and Excel round differently? Does that mean if I insert a formula with VBA it would round differently than a formula entered directly into Excel? I'm starting to hope they don't ask me. %-) Thanks for your reply.

 

[Corgano]

You can also use the ROUNDUP and ROUNDDOWN functions to force Excel to round one way or the other if that's in your expectations of the spreadsheet.

 

[AnotherHiggins]

Tony,

I never knew about the "Banker's Rounding." I'll definitely have to keep that in mind when using Access and VBA. Have a star on me!

John

 

[TonyJollans]

Hi srogers,

Yes, that's possible!

Try this experiment. In a code module enter this function ..

[blue]Function VBARound(ExCell, NumDP)
Application.Volatile
VBARound = Round(ExCell, NumDP)
End Function[/blue]

Now in Cell B1, Enter [purple]=ROUND(A1,2)[/purple]
and in Cell C1, Enter [purple]=VBAROUND(A1,2)[/purple]

And try with different numbers in cell A1, say 32.865 and 32.875 to illustrate.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.