converting text to number

[nxqviet]

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_

 

[Robin Hammond]

Something like this?

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

 

[Debra Dalgleish]

You can use the N function:

=N(A2)*B2

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_

 

[nxqviet]

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

 

[nxqviet]

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


Thanks so much Debra

 

[Debra Dalgleish]

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