| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of minutes not calculated
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) *** |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 MVP http://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) *** |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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. "nlp239" wrote: 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) *** |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 "Joel" wrote in message ... 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. "nlp239" wrote: 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) *** |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 "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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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. -- David Biddulph "nlp239" wrote in message ... ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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. On Nov 7, 9:33*am, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you are still struggling to sort out your problem, you can upload to somewhere likehttp://www.savefile.com/and let us know the url of your file. -- David Biddulph "nlp239" wrote in message ... ... 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.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 "nlp239" wrote in message ... 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. On Nov 7, 9:33 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you are still struggling to sort out your problem, you can upload to somewhere likehttp://www.savefile.com/and let us know the url of your file. -- David Biddulph "nlp239" wrote in message ... ... 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.- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
=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 - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value of minutes not calculated
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to create a calculated item based on a calculated field | Excel Discussion (Misc queries) | |||
| Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
| PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
| pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Linear Mode
