trouble with subtracting cells and adding text...

T

traybuddy

A1=4278.39
Q12=4318.00

MY FORMULA IS...
=(Q12-A1)&"HRS"

IAM GETTING ...
39.6099999999997HRS

WHEN I SHOULD GET...
39.61HRS

IT ONLY HAPPENS WHEN I PUT THE... &"HRS" AT THE END OF THE FORMULA...
 
F

Faz

If you always have 2 decimal places, there should be no harm if you use
the ROUND function.
 
R

Roger Govier

Hi

I think you will need to use
=TEXT((Q12-A1),"0.00")&" HRS"

Regards

Roger Govier
 
C

Cricketer

I agree with Roger except I think you need a /100 in the calculation.
Even if you put in 0.00000000% you still retain the integrity of the
calculation.
I tried several options including Concatenate and all but this method lose
the accuracy of the calculation. Anyone know why?
 
M

Mangesh Yadav

Use:
=text(Q12-A1,"0.00")&" HRS"


Mangesh






Cricketer said:
I agree with Roger except I think you need a /100 in the calculation.
Even if you put in 0.00000000% you still retain the integrity of the
calculation.
I tried several options including Concatenate and all but this method lose
the accuracy of the calculation. Anyone know why?
Click to expand...
 
R

Roger Govier

Hi

A better alternative than my first posting would be to format the cell.
Format>Cells>Number>Custom and in the white pane type #,##.00 "HOURS"
You will then see 39.61 HOURS but the numeric value will still be
39.6099999999997

Regards

Roger Govier
 
Top