How to replace a zero with "Not Available"

[The Needy]

What is the function I need to do the above? Need this ASAP!!!!!!!!! Please
and thank you!!!!!!!!!!

 

[Jacob Skaria]

Select the column
Hit Ctrl+H

In the Find and Replace window

Click Options>Check 'Match entire cell contents'
Find What: 0
Replace with: Not Available

What happened to your previous post; to count instances more than zero.?

If this post helps click Yes

 

[Rick Rothstein]

The answer to your question depends on your setup... Do you have a formula
in the cell that is evaluating to zero? Or is the zero typed in by the user?
Do you want the cell to hold the value of zero, but simply display "Not
Available" to the user? The more details you give us, the better able
someone here can give you the answer you are looking for.

 

[JoeU2004]

What is the function I need to do the above?

If you mean: how to display "Not Available" when the value of a cell is
zero, one way is to use the custom format General;-General;"Not Available".

However, note that the value of the cell will still be zero. So in a
comparison, you would write IF(A1=0,...,...), not IF(A1="Not
Available",...,...).

PS: For future reference, note that it is preferrable to at least repeat
the subject in the body of your message, if not expand upon it. In this
case, you terse question really is unclear and ambiguous.

 

[The Needy]

I put this =IF(ISNA(COUNTIF('Store 1
Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2 Inventory'!$B$7:$B$34,B11)),"Not
Available",(COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B11))), but it still won't give me the "Not Available"
for the zeros...

 

[JoeU2004]

I put this =IF(ISNA(COUNTIF('Store 1
Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B11)),"Not
Available",(COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B11))), but it still won't give me the "Not
Available"
for the zeros...

 

[JoeU2004]

[Sorry for the previous misposting. Fat fingers!]

I put this =IF(ISNA(COUNTIF('Store 1
Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B11)),"Not
Available",(COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+COUNTIF('Store 2
Inventory'!$B$7:$B$34,B11))), but it still won't give me the "Not
Available"
for the zeros.

Why are you using ISNA()?

ISNA() is true only when its argument returns a #NA error. COUNTIF does
not; nor does the sum of the COUNTIFs.

I wonder if you want:

=IF(COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+
COUNTIF('Store 2 Inventory'!$B$7:$B$34,B11) = 0,
"Not Available",
COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+
COUNTIF('Store 2 Inventory'!$B$7:$B$34,B11))

Alternatively, you have simply:

=COUNTIF('Store 1 Inventory'!$B$6:$B$36,B11)+
COUNTIF('Store 2 Inventory'!$B$7:$B$34,B11)

formatted with the custom format General;-General;"Not Available".

However, as noted previously, if you want to test this cell for "Not
Available" elsewhere, you would write IF(A1=0,1,2) instead of IF(A1="Not
Available",1,2).


----- original message -----