Excel Rounding of Formular results

T

Tim Steven

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/tutorial...71-c05dd05ba1fd/handling-binary-and-text.aspx
 
T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top