making time sheet in excel 2003 with formular but when entering text ie sick holiday or course in cells get #value error & stops formular working formular is

=C5-B5+E5-D5+G5-F5+I5-H5+K5-J5+M5-L5+O5-N5-COUNT($B5:$E5:$H5:$K5)/2*(30/(60*24))

made list with sick holiday course in drop down list & conditional format colours

Try using a =SUM() function. =SUM(C5-B5+E5-D5+G5-F5+I5-H5+K5-J5+M5-L5+O5-N5)-COUNT($B5:$E5:$H5:$K5)/2*(30/(60*24))

See here:

http://support.microsoft.com/kb/100825

MIKE

Not only is SUM a good idea, but I like this syntax better: =SUM(C5,E5,G5,I5,K5,M5,O5)-SUM(B5,D5,F5,H5,J5,L5,N5)-COUNT($B5:$E5:$H5:$K5)/2*(30/(60*24))

Don't ask me why, but I just find it easier to follow. All the plus signs and minus signs make my head swim.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

tried this still get #value

tried sum ( ) stil get value fault

tried both formulars still get #value error

Did you read the link I posted? Try this:

=SUM(C5,E5,G5,I5,K5,M5,O5)-SUM(B5,D5,F5,H5,J5,L5,N5)-QUOTIENT(COUNT($B5:$E5:$H5:$K5),2)*(QUOTIENT(30,(60*24)))

MIKE

If it's not working for you, then you'll need to give us some more specifics. I used your formula in A1:

=C5-B5+E5-D5+G5-F5+I5-H5+K5-J5+M5-L5+O5-N5-COUNT($B5:$E5:$H5:$K5)/2*(30/(60*24))

And my formula in A2:

=SUM(C5,E5,G5,I5,K5,M5,O5)-SUM(B5,D5,F5,H5,J5,L5,N5)-COUNT($B5:$E5:$H5:$K5)/2*(30/(60*24))

I put 1's in B5:O5 and both formulas returned -0.104166667

I then tried a few other number combinations and each time both formulas returned the matching results, so we know that they are equivalent.

I then put the word

Holidayin B5 and your formula returned #VALUE while mine returned 0.90625.I then tried Holiday in each column B - O and my formula kept returning numbers while yours always returned #VALUE.

I even tried Holiday in multiple cells, all the way up to having Holiday in

everycell. My formula never returned an error, yours did whenever there was text anywhere in B5:O5.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll