Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
I am getting a #VALUE error when trying to add a series of data in different
cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
I am getting a #VALUE error when trying to add a series of data
Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
Here is the formula:
IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
"inconsistent formula" is *not your problem*.
That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
Biff:
Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
The problem seemed to be with the next column which
had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
Biff:
I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
Explain what you want to multiply. You don't need the SUM function for
multiplication. Also, don't get confused about blank cells versus empty cells. They're 2 different things although most folks use "blank" to mean both. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
=if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),""))
if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. In this case blank cell means no mathematical or text data in either cell. Thanks once more "T. Valko" wrote: Explain what you want to multiply. You don't need the SUM function for multiplication. Also, don't get confused about blank cells versus empty cells. They're 2 different things although most folks use "blank" to mean both. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
We don't seem to be connecting here.
if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. If the result of what was a blank cell, this formula? =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")) If that formula returns a blank and you want to include that formulas cell reference in another formula that gets a total then use the SUM function and it will ignore the blank. Or, do you mean these cells are causing the problem: G1*H1*E1*C1*C7 If those cells are causing the problem what is in those cells? As I noted in my other reply the PRODUCT function will ignore text (which is what a formula blank is) and empty cells. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")) if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. In this case blank cell means no mathematical or text data in either cell. Thanks once more "T. Valko" wrote: Explain what you want to multiply. You don't need the SUM function for multiplication. Also, don't get confused about blank cells versus empty cells. They're 2 different things although most folks use "blank" to mean both. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
OK - sorry about the confusion. If any of the cells G1*H1*E1*C1*C7 the
answer should be zero. There is also a possibility that one of the cells could be empty of data (not text). If I use the multiplication function and one is empty then I get an error statement. If I use the product function then the answer will be whatever cell or cells that have data - which would be incorrect as they have not been multiplied by zero. Hope this helps. "T. Valko" wrote: We don't seem to be connecting here. if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. If the result of what was a blank cell, this formula? =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")) If that formula returns a blank and you want to include that formulas cell reference in another formula that gets a total then use the SUM function and it will ignore the blank. Or, do you mean these cells are causing the problem: G1*H1*E1*C1*C7 If those cells are causing the problem what is in those cells? As I noted in my other reply the PRODUCT function will ignore text (which is what a formula blank is) and empty cells. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")) if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. In this case blank cell means no mathematical or text data in either cell. Thanks once more "T. Valko" wrote: Explain what you want to multiply. You don't need the SUM function for multiplication. Also, don't get confused about blank cells versus empty cells. They're 2 different things although most folks use "blank" to mean both. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE
OK, in other words, if *any cell* within G1,H1,E1,C1,C7 *does not contain a
number* then you want a result of 0 (or blank?)? Try this: =IF(A16="","",IF(AND(A16500,A16<600,COUNT(G1,H1,E 1,C1,C7)=5),G1*H1*E1*C1*C7,"")) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... OK - sorry about the confusion. If any of the cells G1*H1*E1*C1*C7 the answer should be zero. There is also a possibility that one of the cells could be empty of data (not text). If I use the multiplication function and one is empty then I get an error statement. If I use the product function then the answer will be whatever cell or cells that have data - which would be incorrect as they have not been multiplied by zero. Hope this helps. "T. Valko" wrote: We don't seem to be connecting here. if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. If the result of what was a blank cell, this formula? =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")) If that formula returns a blank and you want to include that formulas cell reference in another formula that gets a total then use the SUM function and it will ignore the blank. Or, do you mean these cells are causing the problem: G1*H1*E1*C1*C7 If those cells are causing the problem what is in those cells? As I noted in my other reply the PRODUCT function will ignore text (which is what a formula blank is) and empty cells. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")) if the result was a blank cell then the summary column returned with a #VALUE instead of a "blank" cell. In this case blank cell means no mathematical or text data in either cell. Thanks once more "T. Valko" wrote: Explain what you want to multiply. You don't need the SUM function for multiplication. Also, don't get confused about blank cells versus empty cells. They're 2 different things although most folks use "blank" to mean both. -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: I guess I must be dumber than a rock. How would I use a sum function when I am trying to multiply? Thanks again. David "T. Valko" wrote: The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1 *C7),"")). If the result was a blank cell then the summary column returned with a #VALUE That's why you should use the SUM function. SUM ignores the formula blanks. If: A1 = formula blank A2 = 10 The formula =A1+A2 = #VALUE! While =SUM(A1,A2) = 10 Now, PRODUCT ignores empty cells and formula blanks *but* if there is only a single value available then the result is that single value: A1 = formula blank A2 = empty cell A3 = 10 =PRODUCT(A1:A3) = 10 -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Biff: Thanks for your assistance so far. The problem seemed to be with the next column which had a multiplication formula - =if(a16="","",If(AND(A16500,A16<600),G1*H1*E1*C1* C7),"")). If the result was a blank cell then the summary column returned with a #VALUE. I changed it to use the Product function which solved that issue. Now I am faced with another- this involving the product function not returning a zero when multiplying by zero. If E1 and C1 are blank cells and C7 contains a number it will return the value of C7 times G1 and H1. I am needing it to return a value of zero which in my wretched mind is what it should be doing. If G1=1.44 and h1=0, then the answer should be 0 zero, isnt it? When C1 is blank shouldnt c1*h1 still equal zero as it is "blank" * 0? Thanks for your help. "T. Valko" wrote: "inconsistent formula" is *not your problem*. That's just Excel trying to alert you that all formulas in the immediate are are not the same. That summary column is a simple formula of "I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. You can use the SUM function: =SUM(I17,K17,M17,O17,Q17,S17,U17,W17,Y17,AA17) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... Here is the formula: IF($A22=",",IF(AND($A22600,$A22<700),(VLOOKUP($A2 2,Equipment!$I22:$AE478,16)),")) I understand your comment and will look into it further. There are circumstances where the formula will return a blank cell. There are other cells where data is returned. In both cases the inconsistent formula shows up. These are on columns that I have inserted into the worksheet. The workbook was originally an Excel 2003 format and is now saved under the Excel 2007 format. There are also situations in which the same formula is used and a blank cell occurs and also one in which data is returned buth they do not get the "inconsistent formula" error. This causes the summary column to show the #VALUE error. That summary column is a simple formula of " I17+K17+M17+O17+Q17+S17+U17+W17+Y17+AA17 so I cant use the SUM function as it skips data in columns. "T. Valko" wrote: I am getting a #VALUE error when trying to add a series of data Does your formula look something like this: =A1+B1+C1 If you get a #VALUE! error from a formula like that it usually means that at least one of the referenced cells contains a TEXT entry. Use the SUM function instead, it ignores text: =SUM(A1:C1) -- Biff Microsoft Excel MVP "dpridemore" wrote in message ... I am getting a #VALUE error when trying to add a series of data in different cells. Apparently this is due to an "inconsistent formula" with others in the same area of the worksheet. I have a list of data that I am accessing through IF, AND, and VLOOKUP functions. The cell in one column will not show any "inconsistent formula" errors and provides the correct data. When the formula is moved two cells to the right and the VLOOKUP function adjusted by 2 columns to make sure it picks up the correct data then the "inconsistent formula" error is shown. This is happening on three columns in the worksheet. The good column will pick up data from the table at column 14. The second will pick up on 11. The third on column 10. The third on column 16. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|