Let me start by FIRST apologising to Bernard, it really did give me an
error but I should have double-checked the error.

Thank you David for both pointing-out my error in regards to Bernard
and also SOLVING my problem whilst pointing out what I was doing
wrong. It now works as intended.

Thank you everyone for your patience, assistance and guidance.

On Nov 7, 2:36*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=E3+I3+K3+M3+O3 in your file does not give a #VALUE! error. *If it had done,
as you said, then Bernard would have been correct in his statement. *You
can't expect to get correct answers from experts like Bernard if you give
them incorrect information. *It is also very impolite of you to say "it
would really help if you could give me a little more assistance" when he has
already given the correct answer to the symptoms as you described them.
Perhaps you have forgotten that contributors like Bernard give their time
freely to answer questions such as yours?
--
David Biddulph

"nlp239" wrote in message

...
Well, if it was that easy to identify it would have been easy to fix
however life's not that simple.

All the cell ARE numeric as can be seen by my original post. I don't
want to seem un-appreciative but it would really help if you could
give me a little more assistance.

Thanks.

On Nov 2, 8:56 am, "Bernard Liengme"
wrote:



If =E3+I3+K3+M3+O3 give a #VALUE! error, then one of the cell is not
numeric
That is your problem
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"nlp239" wrote in message

....
On Nov 1, 9:11 am, "Bernard Liengme"
wrote:

You need to check if I3 has been formatted as Text
Somewhere on your worksheet use =ISNUMBER(I3)
If this returns FALSE we have located the problem
Delete the content, reformat the cell as General, re-enter its value

By the way, you are misusing the SUM function. Typically if we need to
sum
a
range of values such as A1:A10 we use =SUM(A1:A10). But you do not need
it
for very arithmetic operation. So your formulas should read:

=SUM(E3+I3+K3+M3+O3) change to =E3+I3+K3+M3+O3
=SUM(C3-B3) change to C3-B3
=IF(D3=0,0,ABS(D3-117)) is OK
=SUM(G3-F3)*24*60 change to =(G3-F3)*24*60
=SUM(J3*10) change to =J3*10
=SUM(L3*10) change to =L3*10
=SUM(N3*3) change to =N3*10
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"nlp239" wrote in message

....

I have the following in a spreadsheet. Everything works great except
the last column, the Total [=SUM(E3+I3+K3+M3+O3)]. The value of cell
I3 is being totally ignored. Thanks for any help.

Column headings:
Team Name
"Starting km"
"Finishing km"
Total km
"Penalty Points Official Distance 117km 1 penalty point for Each km
over/under"
Start Time
Finish Time
Total Time
"Penalty Points Official Length 175 minutes (2h 55m) 1 pt p/m if late
2 pts p/m if early"
Panics opened
Penalties Panics x10
"Wrong side of Envelope opened"
Penalties Envelope x10
"Incorrect/Incomplete Puzzles"
Penalties Puzzles x3
Totals

The data:

Dancer 28502 28642 =SUM(C3-B3) =IF(D3=0,0,ABS(D3-117))
0.736805555555556 0.881944444444445 =SUM(G3-F3)*24*60
=IF(H3=0,0,IF(H3<175,(((175-H3)*2)/1440),IF(H3175,((H3-175)/
1440),IF(H3=175,0)))) 1 =SUM(J3*10) 4 =SUM(L3*10) 0 =SUM(N3*3)
=SUM(E3+I3+K3+M3+O3)

Text following text between *** is cut and paste from the XL sheet

***

Team Name "Starting
km" "Finishing
km" Total km "Penalty Points
Official Distance 117km
1 penalty point for
Each km over/under" Start Time Finish Time Total Time "Penalty Points
Official Length 175 minutes
(2h 55m)
1 pt p/m if late
2 pts p/m if early" Panics opened Penalties Panics x10 "Wrong side of
Envelope
opened" Penalties Envelope x10 "Incorrect/Incomplete
Puzzles" Penalties Puzzles x3 Totals
Dancer 28502 28642 =SUM(C3-B3) =IF(D3=0,0,ABS(D3-117))
0.736805555555556 0.881944444444445 =SUM(G3-F3)*24*60
=IF(H3=0,0,IF(H3<175,(((175-H3)*2)/1440),IF(H3175,((H3-175)/
1440),IF(H3=175,0)))) 1 =SUM(J3*10) 4 =SUM(L3*10) 0 =SUM(N3*3)
=SUM(E3+I3+K3+M3+O3)
***- Hide quoted text -

- Show quoted text -

Thanks a lot people. We seem to be focusing on the formatting instead
of the problem. I use the AutoSum but everyday I learn something new.
I will get out of the habit of using SUM and the + signs little by
little.

Besides, the change to =E3+I3+K3+M3+O3 gave me a #VALUE error.

The =ISNUMBER(I3) returned TRUE so I guess we're back to the original
question.
Should I point-out the formula in I3 is =IF(H3=0,0,IF(H3<175,(((175-
H3)*2)/1440),IF(H3175,((H3-175)/1440),IF(H3=175,0)))) if this makes a
difference.

I'm sure you guys don't like to retype things so, is there a way I can
show you the spreadsheet. Should I upload it and link to it?
Thanks again.
j- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -