A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

#VALUE



 
 
Thread Tools Display Modes
  #11  
Old October 9th 08, 07:48 PM posted to microsoft.public.excel.misc
dpridemore
external usenet poster
 
Posts: 9
Default #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(A16>500,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(A16>500,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(A16>500,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(A16>500,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($A22>600,$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.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

Ads
  #12  
Old October 10th 08, 03:07 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default #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(A16>500,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(A16>500,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(A16>500,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(A16>500,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(A16>500,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($A22>600,$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.
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.