Problem with =IF(AND........ formula
 

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted) and the
other cells are blank, it is returning a "1" when it should return a "0".
What have I done wrong or failed to do???? Please note the formula works
fine when all cells contain data. It seems to be only the G21 cell and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number, G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a
date. All date cells are formatted MMM-YY.

Problem with =IF(AND........ formula
 

Check your cells for " " versus ""
" """ is true
" "0 is true
if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true

"PSU35" wrote:

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted) and the
other cells are blank, it is returning a "1" when it should return a "0".
What have I done wrong or failed to do???? Please note the formula works
fine when all cells contain data. It seems to be only the G21 cell and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number, G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a
date. All date cells are formatted MMM-YY.

Problem with =IF(AND........ formula
 

BJ or Anyone else
I'm not sure what your telling me when you say check my cells for " " vs "".
Let me expand a little and see if this clarifies it better.
Cell B15 is populated via a "VLOOKUP" formula used an IF statement formula.
Cell C7 is populated via the user from a dropdown selection list.
Cell C11 is calculated based on a user entry in a neighboring cell.
Cell E12 always contains a date formatted as mentioned before.



"bj" wrote:

Check your cells for " " versus ""
" """ is true
" "0 is true
if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true

"PSU35" wrote:

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted) and the
other cells are blank, it is returning a "1" when it should return a "0".
What have I done wrong or failed to do???? Please note the formula works
fine when all cells contain data. It seems to be only the G21 cell and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number, G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a
date. All date cells are formatted MMM-YY.

Problem with =IF(AND........ formula
 

for each of the items in the and statement
make a separate euqtion
=$B150

='Proposal Data'!$G$21="YES",

='Proposal Data'!$C$7<=E12

=E12<'Proposal Data'!$C$11

for you to get 1 all of these must be true.

a way for all of these to be true is for B15 and C11 to have spaces " ",
not a blank "" but for C7 to have an actual blank

try
=B15 = ""
and
=b15 = " "
and see what you get
one way tp fix it is

IF(AND(isnumber($B15),isnumber('Proposal Data'!$C$7),isnumber('Proposal
Data'!$C$11),$B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)


"PSU35" wrote:

BJ or Anyone else
I'm not sure what your telling me when you say check my cells for " " vs "".
Let me expand a little and see if this clarifies it better.
Cell B15 is populated via a "VLOOKUP" formula used an IF statement formula.
Cell C7 is populated via the user from a dropdown selection list.
Cell C11 is calculated based on a user entry in a neighboring cell.
Cell E12 always contains a date formatted as mentioned before.



"bj" wrote:

Check your cells for " " versus ""
" """ is true
" "0 is true
if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true

"PSU35" wrote:

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted) and the
other cells are blank, it is returning a "1" when it should return a "0".
What have I done wrong or failed to do???? Please note the formula works
fine when all cells contain data. It seems to be only the G21 cell and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number, G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a
date. All date cells are formatted MMM-YY.

Problem with =IF(AND........ formula
 

Actually even if $B15 = "" $B150 will be TRUE

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"bj" wrote in message
...
for each of the items in the and statement
make a separate euqtion
=$B150

='Proposal Data'!$G$21="YES",

='Proposal Data'!$C$7<=E12

=E12<'Proposal Data'!$C$11

for you to get 1 all of these must be true.

a way for all of these to be true is for B15 and C11 to have spaces " ",
not a blank "" but for C7 to have an actual blank

try
=B15 = ""
and
=b15 = " "
and see what you get
one way tp fix it is

IF(AND(isnumber($B15),isnumber('Proposal Data'!$C$7),isnumber('Proposal
Data'!$C$11),$B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)


"PSU35" wrote:

BJ or Anyone else
I'm not sure what your telling me when you say check my cells for " " vs
"".
Let me expand a little and see if this clarifies it better.
Cell B15 is populated via a "VLOOKUP" formula used an IF statement
formula.
Cell C7 is populated via the user from a dropdown selection list.
Cell C11 is calculated based on a user entry in a neighboring cell.
Cell E12 always contains a date formatted as mentioned before.



"bj" wrote:

Check your cells for " " versus ""
" """ is true
" "0 is true
if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate
as true

"PSU35" wrote:

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted)
and the
other cells are blank, it is returning a "1" when it should return a
"0".
What have I done wrong or failed to do???? Please note the formula
works
fine when all cells contain data. It seems to be only the G21 cell
and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number,
G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12
is a
date. All date cells are formatted MMM-YY.

Problem with =IF(AND........ formula
 

you are correct
if B15 is "" as the result of an equation (as was stated) B150 is true
if it has no entries (as I had tested initially, but had not retested),
B150 is false
I was not aware of the difference.

"Sandy Mann" wrote:

Actually even if $B15 = "" $B150 will be TRUE

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"bj" wrote in message
...
for each of the items in the and statement
make a separate euqtion
=$B150

='Proposal Data'!$G$21="YES",

='Proposal Data'!$C$7<=E12

=E12<'Proposal Data'!$C$11

for you to get 1 all of these must be true.

a way for all of these to be true is for B15 and C11 to have spaces " ",
not a blank "" but for C7 to have an actual blank

try
=B15 = ""
and
=b15 = " "
and see what you get
one way tp fix it is

IF(AND(isnumber($B15),isnumber('Proposal Data'!$C$7),isnumber('Proposal
Data'!$C$11),$B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)


"PSU35" wrote:

BJ or Anyone else
I'm not sure what your telling me when you say check my cells for " " vs
"".
Let me expand a little and see if this clarifies it better.
Cell B15 is populated via a "VLOOKUP" formula used an IF statement
formula.
Cell C7 is populated via the user from a dropdown selection list.
Cell C11 is calculated based on a user entry in a neighboring cell.
Cell E12 always contains a date formatted as mentioned before.



"bj" wrote:

Check your cells for " " versus ""
" """ is true
" "0 is true
if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate
as true

"PSU35" wrote:

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)

My problem is this......if I only have a "YES" (in the cell noted)
and the
other cells are blank, it is returning a "1" when it should return a
"0".
What have I done wrong or failed to do???? Please note the formula
works
fine when all cells contain data. It seems to be only the G21 cell
and that
d_ _ _ "YES" thats causing me the problem.

For reference cells are formatted as follows: ... B15 is a number,
G21 is
"general" displaying blank or "YES", C7 is a date, C11 is a date, E12
is a
date. All date cells are formatted MMM-YY.