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

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?

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

Here's how you can replace all numbers greater than 0 with 1 in Excel:

1. Select the range of cells that you want to convert to presence/absence format.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Custom" category.
4. In the "Type" field, enter the following format code:
   Formula:

   ---

   0;-0;0; 


   ---

5. Click "OK" to close the dialog box and apply the new format to the selected cells.

This format code will display all positive numbers as 1 and all negative numbers as 0. So, any value greater than 0 will be displayed as 1, effectively converting your data set to presence/absence format.

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

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


"Charles" wrote in message
news:FA93245B-4B45-47CF-897E-4E5AC4DBC7FC@microsoftom...
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?

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

On Thu, 10 Jan 2008 10:34:02 -0800, Charles
wrote:

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?

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

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

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

"Tyro" wrote in message
et...
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


"Charles" wrote in message
news:FA93245B-4B45-47CF-897E-4E5AC4DBC7FC@microsoftom...
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?