Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_

Something like this?

=IF(ISNUMBER(A1),A1*B1,0)

--
Robin Hammond
www.enhanceddatasystems.com


"nxqviet" wrote in message
ups.com...
Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_

You can use the N function:

=N(A2)*B2

nxqviet wrote:
Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Robin,

thanks for the comment.

I'm applying a very complex array formula to the cells, and i can't
really use the if(isnumber(...)) approach in this array. i really need
some ways of converting this blank fields to a number format, much like
any other blank field existed in the sheet. these fields are blank but
they are a result of an if statement ...if (x = 0, "",...)

Thanks again

V



On Jan 26, 5:20 pm, "Robin Hammond"
wrote:
Something like this?

=IF(ISNUMBER(A1),A1*B1,0)

--
Robin Hammondwww.enhanceddatasystems.com

"nxqviet" wrote in oglegroups.com...



Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_- Hide quoted text -- Show quoted text -

OMG, that is the shortest answer to the darn problem and it worked!!!


Thanks so much Debra



On Jan 26, 5:28 pm, Debra Dalgleish wrote:
You can use the N function:

=N(A2)*B2

nxqviet wrote:
Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it worked.

nxqviet wrote:
OMG, that is the shortest answer to the darn problem and it worked!!!


Thanks so much Debra



On Jan 26, 5:28 pm, Debra Dalgleish wrote:

You can use the N function:

=N(A2)*B2

nxqviet wrote:

Hi all,

I have a spread sheet with a GetPivotData formula, where I also have an
if statement that replace 0 (zeros) to "" empty cells. This replacement
is critical for some formular to work, but it also make the black cells
not usable for any calculation. If I simply multiply one empty cell by
any number, it would show a #Value! error. How do I avoid this?

I tried Text (...,"#"), Value (...), neither work.

Thanks,

V_--

Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html