Using IF statement in Excel

[nwyork]

I had a survey on the web and I saved the data in numbers in the database. I need to send an excel sheet to the Marketing dept. to analyze the results of the survey. My question is how do I convert say rows B2 to B4000 to text. For example if I have "1" in B2 I want to show "0 - 2 hours per month", if I have "2" I want to show "2 - 5 hours per month" and so on. I need a select case statement, but I don't see this in Excel. I was trying

=IF(B2=1,"0 - 2 hours per month",
IF(B2=2,"2 - 4 hours per month",
IF(B2=3,"5 - 10 hours per month",
IF(B2=4,"10 - 20 hours per month",
IF(B2=5,"Over 20 hours","No Input")))))

I don't know how to select columns B2 through B4000 on this.

Any help will be appreciated.

Thanks,
Nick

 

[SkipVought]

Hi,

1. create another sheet containing
[tt]
0 - 2
2 - 4
5 - 10
10 - 20
Over 20
[/tt]
2. the forumla, lets say in D2
[tt]
D2: =If(iserror(index(Sheet2!$A$1:$A$5,B2,1),"No Input",index(Sheet2!$A$1:$A$5,B2,1)&" hours per month")
[/tt]
copy down.


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Crystalyzer]

How about using Vlookup? You could set a range called "table" with two columns as follows:
1 0 - 2 hours per month
2 2 - 4 hours per month
3 5 - 10 hours per month
4 11 - 20 hours per month
5 Over 20 hours per month

Then put this formula in the column you want to have the text in:

=Vlookup(B1,table,2,false)

if you want to actually replace the information in column be just copy and paste special values into column b.




Thanks and best regards,
-Lloyd

 

[macropod]

Hi nwyork,

FWIW, there seems to be a flaw in your output, since it implies that someone who works for 2, or 10 hours per month can each fit into 2 categories, whilst someone who works more than 4 but less than 5 hours per month doesn't fit into any.

Perhaps the outputs should read:
"no data"
"up to 2 hours per month"
"more than 2 and up to 5 hours per month"
"more than 5 and up to 10 hours per month"
"more than 20 and up to 20 hours per month"
"more than 20 hours per month"

Cheers

 

[GlennUK]

Hey, macropod, watch out for those typos ... quote:

"more than 20 and up to 20 hours per month"

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Touché