Replace numbers to 1s and 0s for presence/absence

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

I have a data set with percent values ranging from 0 to 1. I want to replace
all numbers greater than 0 to 1 in order to convert my data set to
presence/absence. How do I do this?
 
Create a helper column in column B, for example. Assuming your data is in
column A, enter the following formula in B1 = IF(A1,1,0) drag the formula
down column B as far as necessary. When you're satisfied the result in
column B is correct, delete column A.

Tyro
 
I have a data set with percent values ranging from 0 to 1. I want to replace
all numbers greater than 0 to 1 in order to convert my data set to
presence/absence. How do I do this?
Click to expand...

You could also use a macro.

For example:

=====================
Option Explicit
Sub OneZero()
Dim c As Range
For Each c In Selection
c.Value = -(c.Value <> 0)
Next c
End Sub
======================

will convert any non-zero value to a 1; and leave 0's unchanged.

To enter the macro, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code above into the windo that opens.

BACK UP YOUR DATA.

Select your data set
<alt-F8> opens the macro dialog box. Select the Macro and <RUN>.


--ron
 
Whoops!!! Major error!

When you're satisfied the results in column B are correct, copy column B and
paste/special/values into column A and delete column B

Tyro
 
Back
Top