| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value! error in sum function
I need to sum a range of cells. Some of the cells may contain text. If so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Instead of doing something like this:
=A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Instead of doing something like this:
=A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
I have a very similar problem to Art.
In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Try something like this:
=SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Thank you very much T.
Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet. However there are some cases where I have difficulty to know where to put the COUNT(). They look like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0))) All the mentioned cells in row 56 could contain "". Could you help me with this case? I tried to put in one or two COUNT()s but I couldn't get it right. By the way, how would I design the formulas if I wrote the Excel spreadsheet from scratch. Would I really need so many COUNT()s in the formulas? "T. Valko" wrote: Try something like this: =SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Thank you very much T.
With your COUNT() solution I have eliminated most of the #VALUE!s from my worksheet. However there is one remaining which I can't get right. It looks like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0))) Any of the cells in row 56 can and do contain "". I tried to insert some COUNT()s in the formula, but it was not successful. Could you spare a moment to help me with this one? By the way, I wonder how I would formulate the Excel worksheet if I was designing it from scratch. Would I really need to have all these COUNT()s in the formulas - what do other users do? "T. Valko" wrote: Try something like this: =SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Here's how you end up getting a #VALUE! error...
A1 = a formula that might return either a number or a formula blank "". A2 = same thing If either A1 or A2 contain the formula blank then: =A1+A2 will return #VALUE! Excel is telling you that you can't add TEXT values to numeric values. In these cases use the SUM function. SUM will ignore any text entries which is what a formula blank is. =SUM(A1:A2) So, with this formula: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0))) Try it like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0))) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... Thank you very much T. Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet. However there are some cases where I have difficulty to know where to put the COUNT(). They look like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0))) All the mentioned cells in row 56 could contain "". Could you help me with this case? I tried to put in one or two COUNT()s but I couldn't get it right. By the way, how would I design the formulas if I wrote the Excel spreadsheet from scratch. Would I really need so many COUNT()s in the formulas? "T. Valko" wrote: Try something like this: =SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
Thank you T.
Your formula solved my problem. I only had to adapt it, so that where there is a condition with a value 0, I had to change the 0 to "" in some cases, because the conditioning cell may contain "". P.S. Ignore my duplicated posting of the 10/15/2009 - there was a connection problem on that day and I don't know how to remove or change my repeat posting. Thanks again, Lancelot "T. Valko" wrote: Here's how you end up getting a #VALUE! error... A1 = a formula that might return either a number or a formula blank "". A2 = same thing If either A1 or A2 contain the formula blank then: =A1+A2 will return #VALUE! Excel is telling you that you can't add TEXT values to numeric values. In these cases use the SUM function. SUM will ignore any text entries which is what a formula blank is. =SUM(A1:A2) So, with this formula: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0))) Try it like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0))) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... Thank you very much T. Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet. However there are some cases where I have difficulty to know where to put the COUNT(). They look like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0))) All the mentioned cells in row 56 could contain "". Could you help me with this case? I tried to put in one or two COUNT()s but I couldn't get it right. By the way, how would I design the formulas if I wrote the Excel spreadsheet from scratch. Would I really need so many COUNT()s in the formulas? "T. Valko" wrote: Try something like this: =SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#value! error in sum function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lancelot" wrote in message ... Thank you T. Your formula solved my problem. I only had to adapt it, so that where there is a condition with a value 0, I had to change the 0 to "" in some cases, because the conditioning cell may contain "". P.S. Ignore my duplicated posting of the 10/15/2009 - there was a connection problem on that day and I don't know how to remove or change my repeat posting. Thanks again, Lancelot "T. Valko" wrote: Here's how you end up getting a #VALUE! error... A1 = a formula that might return either a number or a formula blank "". A2 = same thing If either A1 or A2 contain the formula blank then: =A1+A2 will return #VALUE! Excel is telling you that you can't add TEXT values to numeric values. In these cases use the SUM function. SUM will ignore any text entries which is what a formula blank is. =SUM(A1:A2) So, with this formula: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0))) Try it like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0))) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... Thank you very much T. Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet. However there are some cases where I have difficulty to know where to put the COUNT(). They look like this: =IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0))) All the mentioned cells in row 56 could contain "". Could you help me with this case? I tried to put in one or two COUNT()s but I couldn't get it right. By the way, how would I design the formulas if I wrote the Excel spreadsheet from scratch. Would I really need so many COUNT()s in the formulas? "T. Valko" wrote: Try something like this: =SUM(A1:B1,IF(COUNT(C1),-C1)) -- Biff Microsoft Excel MVP "Lancelot" wrote in message ... I have a very similar problem to Art. In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet and then I get a heap of #VALUE! results in Excel. The reason for this is because the 1-2-3 formulas contain a lot of ""s, which is text and can only be added by Excel using =sum, or so I understood. Unfortunately, =sum doesn't work when several non-contiguous cells are added and the "" cell is deducted. Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition not having been met in C1). The =sum formula shows #VALUE! only because the "" in C1 is deducted instead of added. What do I have to put in my C1 formula so that, when the condition there is not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not allowed to show zero because that is an incorrect value. What is the best solution? "T. Valko" wrote: Instead of doing something like this: =A1+A2+A3 Do it like this: =SUM(A1:A3) The SUM function ignores text. -- Biff Microsoft Excel MVP "Art" wrote in message ... I need to sum a range of cells. Some of the cells may contain text. If so, the function returns #value!. In Lotus 1-2-3, text is treated as 0 for function purposes. Is there some way to force Excel to do the same, or another way to get around this problem? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function error | Excel Worksheet Functions | |||
| if function error | Excel Worksheet Functions | |||
| Function error | Excel Worksheet Functions | |||
| IF THEN function error | Excel Worksheet Functions | |||
| IF function with #N/A error | Excel Worksheet Functions | |||
Linear Mode
