Excel Rounding of Formular results
 

I am converting minutes to Hrs & Secs but having trouble!

I have the below forumla however the minutes keep rounding out to 10 or more decimal places.

=INT(V23/60)&"hrs "&MOD(V23,60)&"mins"

Cell V23 = 239

Cell W23 is using the above formular which keeps spitting out heaps of decimal places for minutes

3hrs 58.9999999999999mins


I am calculating cell V23 from cell R23 & S23 which is the difference between 2 times as shown in formular below.

=IF(ISERROR((S23-R23)*1440)," ",(S23-R23)*1440)

Not sure if this is affecting my result in cell W23.

Just new at excel and would love some help please.




Submitted via EggHeadCafe - Software Developer Portal of Choice
HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE
http://www.eggheadcafe.com/tutorials...-and-text.aspx

Excel Rounding of Formular results
 

The problem is that the formula result of 239 is not *exactly* 239.

If you try this formula you'll see what I mean:

=(V23-239)

The result should be 0 but it's not. So, you need to use rounding in the
formula.

Others can explain the "why" much better than I can but it boils down to a
"rounding error" when doing binary math on decimal numbers.

=IF(ISERROR((S23-R23)*1440)," ",(S23-R23)*1440)

Not sure why you're using the ISERROR part?

=IF(ISERROR((S23-R23)*1440),"",ROUND((S23-R23)*1440,0))

Now you're other formula will return 3hrs 59mins

--
Biff
Microsoft Excel MVP


<Tim Steven wrote in message ...
I am converting minutes to Hrs & Secs but having trouble!

I have the below forumla however the minutes keep rounding out to 10 or
more decimal places.

=INT(V23/60)&"hrs "&MOD(V23,60)&"mins"

Cell V23 = 239

Cell W23 is using the above formular which keeps spitting out heaps of
decimal places for minutes

3hrs 58.9999999999999mins


I am calculating cell V23 from cell R23 & S23 which is the difference
between 2 times as shown in formular below.

=IF(ISERROR((S23-R23)*1440)," ",(S23-R23)*1440)

Not sure if this is affecting my result in cell W23.

Just new at excel and would love some help please.




Submitted via EggHeadCafe - Software Developer Portal of Choice
HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE
http://www.eggheadcafe.com/tutorials...-and-text.aspx