How to replace a zero with "Not Available"

  • Thread starter Thread starter The Needy
  • Start date Start date
T

The Needy

What is the function I need to do the above? Need this ASAP!!!!!!!!! Please
and thank you!!!!!!!!!!
 
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
 
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.
 
The Needy said:
What is the function I need to do the above?
Click to expand...

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.
 
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...
 
The Needy said:
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...
Click to expand...
 
[Sorry for the previous misposting. Fat fingers!]

The Needy said:
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.
Click to expand...

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 -----
 
Back
Top