#Value Error when sourcing data from...

....Negative conditional formatted cell In MS Excel.

Hi guys,

I haven't been very busy at work lately and the boss has told me do try and do 'something' productive :P - So I thought I would create my own calendar which would keep track of hours, overtime and the amount I am getting paid pre/post tax :)

Not being a programmer or all that familiar with excel, I have to admit that it looks pretty good, but I am sure there are better, even free programs of the same kind floating out there... Nevertheless this is a good chance for me to learn new stuff, whilst keeping me busy during the dull moments! :D

===

Anyway, onto the important stuff, as I don't want to bore you :P

Below is a link to an online excel based program called Edit Grid. I have added a mock example of what I am trying to achieve and allowed Read/write access for public use.

If you are able to help me, then please post the solution in here, incase someone else goes in there and then changes it again! I will also outline the formulas used below, incase, again, someone messes with it :P

http://www.editgrid.com/explore/user/klis-test/TT_Error_Test

In brief:

--Cells B3/4,C3/4,D3/4,E3/4 Represent a 4 day week of 8 hour working days.

I do work 5 days, but overtime is not permitted on Fridays and is there for not required for the calculation.

--Cell F3/4 has an conditional equation which adds the hours from week 1 or 2 and then subtracts 32 (8x4) to then calculate the over-time accumulated.

So normally, an empty week would read blank in Cells B3-E4 and as the week progresses I will be adding 8+ hours per day.

Whilst Cells B3-E4 are blank, F3/4 would read -32. I didn't want a negative figure appearing so I have added an IF equation to blank out any figure less than 0.

--Cell H3 represents the total overtime accumulated over the 2 week period.

The problem that I am facing here, is that when the calculation pulls data from the IF equation when it is sub zero, it brings up the error #Value.

When 8 hours or more are added to a day in both Week 1 and 2, then the equation settles and works fine.
(You can check this for your self)


Equations used:

F3: =if(B3+C3+D3+E3-32>0,B3+C3+D3+E3-32,"")
F4: =if(B4+C4+D4+E4-32>0,B4+C4+D4+E4-32,"")
H3: =sum(F3+F4)

I would also like H3 to not show a negative numeric and only show the accumulated over time for the 2 week period.

Thank you all in advance and sorry for perhaps complicating the issue even further lol

Kind regards
Klis