how to write two criteria in if statement?
On May 30, 2:59*pm, "joeu2004" wrote:
"Cindy Wang" wrote:
On May 30, 11:54 am, "joeu2004" wrote:
"Cindy Wang" wrote:
IF(OR(N31-N9)=0,N31=" ")," ",N31-N9)
The correct syntax is:
IF(OR(N31-N9=0,N31=" ")," ",N31-N9)
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" wrote:
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.
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))
It looks great, but could you explain to me the first one "OR(N(N31",
what this part does? Thanks,
|