How to Change any quantity greater than 0 to Yes in excel

How would I change any cell in a work sheet with a quantity greater than 0 to the word "YES"

3 Answers

Create a column next to the column with the cells.

Insert a formula that looks like this, assuming the cells are in column A, you would place this formula in cell B1:

=IF(A1>0;"YES";A1)

Press Enter after typing the formula, and select the cell again. Drag the small square in the bottom right of that cell all the way down until all cells have their new values.

Now select Column B and copy its content. Select column A and Paste special: paste values.

Now remove column B. Note, any previous value in A1 is lost. This is unavoidable or simply use 2 columns.

To change the values "in place" try this small macro:

Sub dural() Dim v As Variant For Each r In ActiveSheet.UsedRange v = r.Text If v <> "" Then If IsNumeric(v) Then If v > 0 Then r.Value = "YES" End If End If Next r
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

and

(v=office.14).aspx

Macros must be enabled for this to work!

To DISPLAY numbers less than zero as "No", Zero as "Neither" and greater than zero as "Yes":

1. Type a set of numbers in any cells, then select the cells.
2. Press and old CTRL then hit 1.
3. Find "Custom" and click on it
4. Now there is a field on the right side where you can enter a 'custom format code'
5. Type "Yes";"No";"Neither" into that field.
6. Click "OK"