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