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

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

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

The correct syntax is:

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

On May 30, 11:54*am, "joeu2004" wrote:
"Cindy Wang" wrote:
Could anyone tell me what went wrong with my if
statement below?
[....]
IF(OR(N31-N9)=0,N31=" ")," ",N31-N9)

The correct syntax is:

IF(OR(N31-N9=0,N31=" ")," ",N31-N9)
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,

"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))

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,

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

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)