Excel question

[twebber]

I have a data set with 538 rows. There's a column of values that if they're
less than 4.18, I want to replace it with 0, above 5.76 replace it with 2,
and in between replace it with 1. How can I do this without manually typing
each one? I googled this and got some info that looked like I'd have to type
up some code, but it seems that there should be an easier way. Any help is
appreciated - thanks!

 

[Dave Peterson]

I'd insert a new column to the right of this column.

Then use a formula:
=if(a1<4.18,0,if(a1<=5.76,1,2))

or if there could be text:
=if(isnumber(a1),if(a1<4.18,0,if(a1<=5.76,1,2)),A1)

or if there could be text and empty cells:
=if(a1="","",IF(ISNUMBER(A1),IF(A1<4.18,0,IF(A1<=5.76,1,2)),A1))

Then drag this down the helper column.
Edit|copy and then
Edit|paste special|values over the original range.
Then delete the helper column.

 

[twebber]

Thankyou so much for the reply

That was very helpful - it worked great. I have another one for you though,
if you wouldn't mind. I thought I knew excel, but I'm still learning I
guess. I want to know how to count how many entries there are that meet two
criteria. For instance, headings are gender and ethnicity. Gender is 1 or 2,
ethnicity is 1-4. How can I know how many 1-1's, 1-2's, 1-3's etc there are?
I think I should use "Countif", but that only seems to work for one column
at a time.

 

[Dave Peterson]

If you're using xl2007, you could use:
=countifs()
(notice the s!)

But in older versions (well, in xl2007, too), you could use =sumproduct().

=sumproduct(--(a1:a10=1),--(b1:b10=1))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===========
But you may want to look at using a pivottable.

in xl2003 menus:
Add headers in row 1
Select the range
A1:B###
Data|Pivottable
Follow the wizard until you get to a step with a Layout Button on it.
Click that layout button
Drag the header for the Gender to the row area
Drag the header for the Ethnicity to the row area
Drag the header for the Gender to the Data area
(rightclick on it and choose Count)
and finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

Thankyou so much for the reply

That was very helpful - it worked great. I have another one for you though,
if you wouldn't mind. I thought I knew excel, but I'm still learning I
guess. I want to know how to count how many entries there are that meet two
criteria. For instance, headings are gender and ethnicity. Gender is 1 or 2,
ethnicity is 1-4. How can I know how many 1-1's, 1-2's, 1-3's etc there are?
I think I should use "Countif", but that only seems to work for one column
at a time.

I'd insert a new column to the right of this column.

Then use a formula:
=if(a1<4.18,0,if(a1<=5.76,1,2))

or if there could be text:
=if(isnumber(a1),if(a1<4.18,0,if(a1<=5.76,1,2)),A1)

or if there could be text and empty cells:
=if(a1="","",IF(ISNUMBER(A1),IF(A1<4.18,0,IF(A1<=5.76,1,2)),A1))

Then drag this down the helper column.
Edit|copy and then
Edit|paste special|values over the original range.
Then delete the helper column.

[Image]