how to write two criteria in if statement?

  • Thread starter Thread starter Cindy Wang
  • Start date Start date
C

Cindy Wang

Could anyone tell me what went wrong with my if statement below?
Thanks,

IF(OR(N31-N9)=0,N31=" ")," ",N31-N9)
 
Cindy Wang said:
Could anyone tell me what went wrong with my if
statement below? [....]
IF(OR(N31-N9)=0,N31=" ")," ",N31-N9)
Click to expand...

The correct syntax is:

IF(OR(N31-N9=0,N31=" ")," ",N31-N9)
 
Cindy Wang said:
Could anyone tell me what went wrong with my if
statement below? [....]
IF(OR(N31-N9)=0,N31=" ")," ",N31-N9)
Click to expand...

The correct syntax is:

IF(OR(N31-N9=0,N31=" ")," ",N31-N9)
Click to expand...
I have a fomrula as below,
=IF((VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D
$24,4,FALSE))=0,"",VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$S$25,9,FALSE)/VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$D$24,4,FALSE)) It will retrun empty cell if there is no value.
But in my next calculation, I need to exclude that empty cell out, but
I don't know how to define that empty cell. Anybody now? Thanks,
 
Errata.... That will result in a #VALUE error if N31 is non-numeric. Two
alteratives, whichever you prefer:


IF(OR(N(N31)-N9=0,N31=" ")," ",N31-N9)

or

IF(N31="", "", IF(N31-N9=0, "", N31-N9))


Cindy Wang said:
I have a fomrula as below,
=IF((VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D
$24,4,FALSE))=0,"",VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$S$25,9,FALSE)/VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$D$24,4,FALSE))
It will retrun empty cell if there is no value.
But in my next calculation, I need to exclude that empty
cell out, but I don't know how to define that empty cell.
Click to expand...

Funny: I was going to comment on your use of " " (one space) instead of ""
(null string) in the formula above. But I chose not to "complicate"
matters.

Anyway, the formula above returns a null string (""), not an "empty cell".
(An "empty cell" is a cell with no formula and no constant; literally
empty.)

So perhaps you want:

IF(OR(N(N31)-N9=0,N31=""),"",N31-N9)

N31="" is TRUE if N31 is empty or it contains the null string (""),
presumably returned from the VLOOKUP formula.

FYI, your VLOOKUP formula is incorrect. It will return an Excel #N/A error
if no match is found.

If you have Excel 2007 or later, you can write:

=IFERROR(VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$S$25,9,FALSE)
/ VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE), "")

If have Excel 2003 or earlier (or you save to a xls file), you might write:

=IF(ISNUMBER(MATCH(M27,'[Sales Workpaper
2012.xls]May-12'!$A$8:$A$24,0))=FALSE,
"", VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$S$25,9,FALSE)
/ VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE))
 
Errata....  That will result in a #VALUE error if N31 is non-numeric.  Two
alteratives, whichever you prefer:

IF(OR(N(N31)-N9=0,N31=" ")," ",N31-N9)

or

IF(N31="", "", IF(N31-N9=0, "", N31-N9))

Cindy Wang said:
I have a fomrula as below,
=IF((VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D
$24,4,FALSE))=0,"",VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$S$25,9,FALSE)/VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A
$8:$D$24,4,FALSE))
It will retrun empty cell if there is no value.
But in my next calculation, I need to exclude that empty
cell out, but I don't know how to define that empty cell.
Click to expand...

Funny:  I was going to comment on your use of " " (one space) instead of ""
(null string) in the formula above.  But I chose not to "complicate"
matters.

Anyway, the formula above returns a null string (""), not an "empty cell"..
(An "empty cell" is a cell with no formula and no constant; literally
empty.)

So perhaps you want:

IF(OR(N(N31)-N9=0,N31=""),"",N31-N9)

N31="" is TRUE if N31 is empty or it contains the null string (""),
presumably returned from the VLOOKUP formula.

FYI, your VLOOKUP formula is incorrect.  It will return an Excel #N/A error
if no match is found.

If you have Excel 2007 or later, you can write:

=IFERROR(VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$S$25,9,FALSE)
/ VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE), "")

If have Excel 2003 or earlier (or you save to a xls file), you might write:

=IF(ISNUMBER(MATCH(M27,'[Sales Workpaper
2012.xls]May-12'!$A$8:$A$24,0))=FALSE,
"", VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$S$25,9,FALSE)
/ VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE))
Click to expand...

It looks great, but could you explain to me the first one "OR(N(N31",
what this part does? Thanks,
 
Cindy Wang said:
It looks great, but could you explain to me the
first one "OR(N(N31", what this part does?
Click to expand...

Sorry, my oversight. It is difficult to find the Help page for the N()
function.

N(N31) returns zero if N31 is text (like the null string ""); otherwise, N31
returns the numeric value of N31 if it is numeric.

N(N31) is equivalent to: IF(ISTEXT(N31),0,N31)
 
Sorry, my oversight.  It is difficult to find the Help page for the N()
function.

N(N31) returns zero if N31 is text (like the null string ""); otherwise, N31
returns the numeric value of N31 if it is numeric.

N(N31) is equivalent to:  IF(ISTEXT(N31),0,N31)
Click to expand...

Thanks very much for your help. I have one more question:
=IF(ISNUMBER(MATCH(M27,'[Sales Workpaper
2012.xls]May-12'!$A$8:$A$24,0))=FALSE,
"", VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$S
$25,9,FALSE)
/ VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D
$24,4,FALSE)) , Why you have to use a match funtion here for?
Thought match is to look for position. I am not looking for any
position here, am I? Thanks,
 
Cindy Wang said:
I have one more question:
=IF(ISNUMBER(MATCH(M27,'[Sales Workpaper
2012.xls]May-12'!$A$8:$A$24,0))=FALSE, [....]
Why you have to use a match funtion here for?
Thought match is to look for position. I am not
looking for any position here, am I?
Click to expand...

You originally wrote:

=IF((VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE))=0,

I misinterpreted your intent. Use your original conditional expression.

One minor suggestion: eliminate the unnecessary parentheses. Just write:

=IF(VLOOKUP(M27,'[Sales Workpaper 2012.xls]May-12'!$A$8:$D$24,4,FALSE)=0,

More readable; ergo, less error-prone.
 
Back
Top