Value of minutes not calculated

[nlp239]

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(H3>175,((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(H3>175,((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)
***

 

[Bernard Liengme]

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

 

[Joel]

First the formula should not have plus signs. Seperate cells with commas

=SUM(E2,I2,K2,M2,O2)

second, make sure the row is correct . If your headers are in row 1 then
the data is in row 2, not row 3.

 

[Bernard Liengme]

You are correct that the syntax is poor but it is not the likely cause of
the problem since
=SUM(A1+D1+G1) will give the same values as =SUM(A1,D1,G1) if all cells hold
numbers.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

First the formula should not have plus signs. Seperate cells with commas

=SUM(E2,I2,K2,M2,O2)

second, make sure the row is correct . If your headers are in row 1 then
the data is in row 2, not row 3.

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(H3>175,((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(H3>175,((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)
***

 

[nlp239]

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 needit
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

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(H3>175,((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(H3>175,((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(H3>175,((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

 

[Bernard Liengme]

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 MVP
http://people.stfx.ca/bliengme
remove caps from email

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

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(H3>175,((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(H3>175,((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(H3>175,((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

 

[nlp239]

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.

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

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

news:e6e6754c-c9fd-47de-8b94-02f1055def65@u18g2000pro.googlegroups.com....

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(H3>175,((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(H3>175,((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(H3>175,((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 -

 

[David Biddulph]

If you are still struggling to sort out your problem, you can upload to
somewhere like http://www.savefile.com/ and let us know the url of your
file.

 

[nlp239]

Thanks ever so much for not giving-up on me.
The file is located at http://www.savefile.com/files/1875657 and has a
line of real data.

 

[David Biddulph]

I3 is not being ignored. If you didn't count I3, your total would be 11.
If you format your total cell as general it shows 11.0125.
0.0125 is the value in I3. Perhaps you had forgotten that I3 is a time
being displayed in an [m] format? If you want to include the number of
minutes from I3 in your total, then change your =SUM(E3+I3+K3+M3+O3) to
=E3+I3*24*60+K3+M3+O3
The other option is to remove the division by 1440 from your formula in I3,
and then I3 will be a number in minutes, not a time. You seem to be making
life complicated for yourself by multiplying by 1440 on H3 and then dividing
again in I3. H3 is in minutes, but I3 is back to being a time. If you are
working in a mixture of times and numbers of minutes, you need to remember
which of your cells is which.
--
David Biddulph

Thanks ever so much for not giving-up on me.
The file is located at http://www.savefile.com/files/1875657 and has a
line of real data.

 

[David Biddulph]

=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

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.

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

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

news:e6e6754c-c9fd-47de-8b94-02f1055def65@u18g2000pro.googlegroups.com...

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(H3>175,((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(H3>175,((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(H3>175,((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 -

 

[nlp239]

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.

=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


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.

If =E3+I3+K3+M3+O3 give a #VALUE! error, then one of the cell is not
numeric
That is your problem
best wishes

"nlp239" <[email protected]> wrote in message

On Nov 1, 9:11 am, "Bernard Liengme" <[email protected]>
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

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(H3>175,((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(H3>175,((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(H3>175,((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 -