Rounding Problem

[Patrick Simonds]

I am using the following formula:

ROUND((BH24-BG24)*24,1)

Where BH24 = 15:21, BG24 = 15:00 (times based on a 24 hour clock)

The answer returned is 0.3, but it should be 0.4. If you round out to 2
places the answer is 0.35. Unless I am missing something 0.35 should round
to 0.4. If I use Roundup, it rounds everything up. What am I missing?

 

[Frank Kabel]

Hi
this is due to Excel's representation of numbers (see:
http://www.cpearson.com/excel/rounding.htm)

In your case the formula
=(BH24-BG24)*24 does not return 0.35 but
0.3499999999999
(Just test it and format the resulting cell with enough decimal points)

So one solution would be to add a small amount to your formula. e.g.
try
=ROUND((BH24-BG24)*24+0.000000001,1)

 

[Ron Rosenfeld]

I am using the following formula:

ROUND((BH24-BG24)*24,1)

Where BH24 = 15:21, BG24 = 15:00 (times based on a 24 hour clock)

The answer returned is 0.3, but it should be 0.4. If you round out to 2
places the answer is 0.35. Unless I am missing something 0.35 should round
to 0.4. If I use Roundup, it rounds everything up. What am I missing?

It has to do with the way Excel stores numbers in accord with the IEEE
conventions. See numerous discussion on Rounding Errors in the newsgroups, in
the MSKB, and on Chip Pearson's web site.

Another possible solution would be to first round to the nearest minute, then
round your result:

=ROUND(ROUND((BH24-BG24)/TIME(0,1,0),0)*TIME(0,1,0)*24,1)

or, if you have the Analysis Toolpak installed:

=ROUND(MROUND(BH24-BG24,TIME(0,1,0))*24,1)


--ron

 

[Patrick Simonds]

Thanks for the timely responses. I had the Analysis pack installed so I went
with option 2.