#value error

December 5, 2010 at 15:37:52
Specs: Windows 7, 2 gig
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

See More: #value error

Report •


#1
December 5, 2010 at 16:00:11
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

http://www.skeptic.com/


Report •

#2
December 5, 2010 at 16:20:55
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 this How-To.


Report •

#3
December 5, 2010 at 16:45:44
tried this still get #value

Report •

Related Solutions

#4
December 5, 2010 at 16:46:51
tried sum ( ) stil get value fault

Report •

#5
December 5, 2010 at 17:15:26
tried both formulars still get #value error

Report •

#6
December 5, 2010 at 17:15:47
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

http://www.skeptic.com/


Report •

#7
December 5, 2010 at 19:57:57
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 Holiday in 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 every cell. 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 this How-To.


Report •


Ask Question