Excel randomly flagging a result equal to 0.0005 when supposed to flag 0.0005

Question

Hi there,I use excel to calculate weight differences on filtration papers.Basically, samples are heated once, weighed, reheated, and weighed another time.Difference has to be less than or equal to 0.0005g to consider the weight stable.My formulas are for row 4:&nbsp;=ABS(F4-G4) (F4 and G4 are the weights)If ABS(F4-G4)&lt;0.0005, then the calculation is done :&nbsp;=IF(AND(H4&gt;0.0005,I4&lt;&gt;""),1000*(I4-D4),IF(AND(H4&lt;=0.0005,I4=""),1000*(G4-D4),""))If ABS(F4-G4)&gt;0.0005, then the calculation isn't done and filter paper is reheated and reweighted.3rd weight is then entered in I4 and used for the calculation.My issue is that excel will randomly flag data when difference is equal to 0.0005 mg/L.For example, I ran two samples yesterday and weights were 0.5086g and 0.5081g. The difference is 0.0005 but excel treats it as &gt;0.0005.Any idea why it happens?There is another set of weights in my batch that is 0.4283g and 0.4278g and Excel is not flagging it.Thanks!&nbsp;

hansvogelaar · Answer

Cdecoupigny&nbsp;
Try using ROUND(ABS(F4-G4),4) instead of ABS(F4-G4)

cdecoupigny · Answer

That works! Thanks so much!

nothing_left_to_lose · Answer

Cdecoupigny&nbsp;&nbsp;Binary and Decimal arithmetic can have slight differences.The numbers returned by your formulas may be slightly larger or smaller than it what is displayed.Format your numbers to display 15 decimal places just to see what is going on.&nbsp;The solution would be to alter your criteria (0.0005001 for instance) or to Round the numbers to 4 places.&nbsp;&nbsp;'---Nothing Left to Losehttps://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU(free excel programs)

Forum Discussion

Excel randomly flagging a result equal to 0.0005 when supposed to flag >0.0005

Share

Resources