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

P

PSU35

I have the following formula on one sheet that reads another sheet to
determine a value:
=IF(AND($B$15>0,'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.
 
B

bj

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
 
P

PSU35

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.
 
B

bj

for each of the items in the and statement
make a separate euqtion
=$B15>0

='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$15>0,'Proposal Data'!$G$21="YES",'Proposal
Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0)
 
S

Sandy Mann

Actually even if $B15 = "" $B15>0 will be TRUE

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
B

bj

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top