Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Good morning,
{=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
It appears that the formula sums those cells in a portion of the column that
are negative. -- Gary''s Student - gsnu200804 "BeveyG" wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Hi,
It sums the negative values in the range in the formula. to enter it you must enter this =SUM(IF($L$19:$L$148<0,$L$19:$L$148,0)) and then press CTRL+Shift+Enter and Excel will piut the curly brackets {} around the formula. You can't type these yourself. Mike "BeveyG" wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Hi Gary's Student,
That is correct, how can I fix it so it does not produce an error every month? I am perplexed because other columns in the spreadsheet have negative numbers and positive numbers as well, and the formula does not receive an error in those columns??????????????????? OK, I'm now going bald from pulling my hair out... :o) JK Thanks for your response. Bevey G "In Memory of those who lost their lives on this day 7 years ago - may we someday have Peace on Earth and Good Will Toward All Men." "Gary''s Student" wrote: It appears that the formula sums those cells in a portion of the column that are negative. -- Gary''s Student - gsnu200804 "BeveyG" wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
The surrounding { } indicate it is an array formula entered with CTRL +
SHIFT + ENTER What would you like to troubleshoot? Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 09:01:01 -0700, BeveyG wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Hi Mike,
Thank you for your response. I did as suggested but I am still receiving a #VALUE!error. Could this be caused by a cell that has a calculation in it 2,265-1043.6 and the cell value is 1221.40 could this effect the formula. I so appreciate your help Mike. Thanks. BeveyG "Mike H" wrote: Hi, It sums the negative values in the range in the formula. to enter it you must enter this =SUM(IF($L$19:$L$148<0,$L$19:$L$148,0)) and then press CTRL+Shift+Enter and Excel will piut the curly brackets {} around the formula. You can't type these yourself. Mike "BeveyG" wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
If you don't have any errors in that range, then:
=SUMIF($L$19:$L$148,"<"&0) or =SUMIF($L$19:$L$148,"<0") should work ok. (this is not an array formula.) BeveyG wrote: Hi Gary's Student, That is correct, how can I fix it so it does not produce an error every month? I am perplexed because other columns in the spreadsheet have negative numbers and positive numbers as well, and the formula does not receive an error in those columns??????????????????? OK, I'm now going bald from pulling my hair out... :o) JK Thanks for your response. Bevey G "In Memory of those who lost their lives on this day 7 years ago - may we someday have Peace on Earth and Good Will Toward All Men." "Gary''s Student" wrote: It appears that the formula sums those cells in a portion of the column that are negative. -- Gary''s Student - gsnu200804 "BeveyG" wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Hi Gord,
Can you please look at the message above from me responding to Mike? I am really perplexed by this and why I am getting the error when all other columns in the spread sheet have the same values both negative and positive. Thanks so much, BeveyG "Gord Dibben" wrote: The surrounding { } indicate it is an array formula entered with CTRL + SHIFT + ENTER What would you like to troubleshoot? Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 09:01:01 -0700, BeveyG wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
You have several other replies addressing this problem.
Have a look at them. Gord On Thu, 11 Sep 2008 10:17:01 -0700, BeveyG wrote: Hi Gord, Can you please look at the message above from me responding to Mike? I am really perplexed by this and why I am getting the error when all other columns in the spread sheet have the same values both negative and positive. Thanks so much, BeveyG "Gord Dibben" wrote: The surrounding { } indicate it is an array formula entered with CTRL + SHIFT + ENTER What would you like to troubleshoot? Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 09:01:01 -0700, BeveyG wrote: Good morning, {=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))} Can someone help me troubleshoot the above formula. It is used on a worksheet in a multi-function workbook (created by someone else - who is no longer with the company and I can not ascertain their goal). The above formula is used in a Trend Log for Change Orders, Pending Issues, etc. which tracks debits and credits for each in one column. The above formula appears in the cell just as it is shown above????? Help! I have beaten my brain to death.... Thanks. BeveyG "In Memory of those who lost their lives - may we someday have Peace on Earth and Good Will Toward All Men." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|