Error with IF Statement

**Malice** · 10-01-2010, 08:00 AM

I'm using the following formula to work out any variances on workers timesheets.
=IF(SUM(E4:G4)=0,0,(E4+F4+G4)-(B3*4))

E4 - Hours worked
F4 - Holiday hours
G4 - Sick hours
B3 - Weekly contracted hours * weeks in the month

This works fine until I try and adapt the formula for employees who work term time only.
I have simply worked out which weeks they will be off and not counted those weeks in the formula.
e.g. they will only be working 3 weeks in October so I used:
=IF(SUM(E4:G4)=0,0,(E4+F4+G4)-(B3*3))

This gives me the error 1.42109E-14

Does anyone know how I can get this to work?

Edit - I've just tried multiplying it by different numbers and it works with them all apart from the ones I need?

**arthurbr** · 10-01-2010, 08:15 AM

Maybe posting a small sample sheet would help

**Malice** · 10-01-2010, 08:21 AM

Added a sample sheet.

I've tried it with a few different numbers and it looks like it works as long as the answer isn't 0?

**arthurbr** · 10-01-2010, 08:48 AM

If you change manually the value in E4 it's also ok. So it probably has to do with XL's floating point arithmetic rules. As the value of E4 is drawn from another workbook, I don't know why, but you can always embed I33 formula in =round ( your_formula,2).
This will probably solve the issue

Also see http://support.microsoft.com/kb/78113

**Malice** · 10-01-2010, 10:40 AM

Thank you. The cell isn't normally in a different workbook, I just copied a couple of sheets as a sample.

I tried what you said: =round ( your_formula,2) which didn't work, I can manually recalculate the workbook but I was just wondering why the '2' is in the formula?

Thank you for your help.

Edit -

I've tried manually recalculating on the full work book and it doesn't seem to work? I've attached the full workbook if anyone can help.

Edit - Got it to work, thank you

**arthurbr** · 10-01-2010, 11:03 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save