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,