IF function with multiple criteria

[Horus73]

Hi,
How will the formula be in the following situation?

Cel A1- 3 options for input
Jean, Pete, Jim


C A2 - If function in which I want to combine three ifs
=IF(A1="jean";"+4 points";"")
=IF(A1="Pete";"+2 points";"")
=IF(A1="Jim";"+5 points";"")

Thus, CA2 should contain one IF formula. If Jim is entered in A1 then it will give +5 points, but if Pete is entered then it will show +2 points ...and so on.

Thanks again!
Marc

 

[santeria]

Multiple IF Formula pointers.... May Help

http://www.mrexcel.com/board2/viewtopic.php?t=54804&highlight=formula+three+criteria

Bundles more in the archives.

 

[LTunnicliffe]

=if(A1="jean","+4 points",if(A1="Pete","+2 points",if(A1="Jim","+5 points","Name not found")))

Hope this helps!

 

[Horus73]

Thanks, is there a maximum on the number of criteria in one IF formula?
I get the impression there is, based on above mentioned search.

Marc

 

[Aladin Akyurek]

Thanks, is there a maximum on the number of criteria in one IF formula?
I get the impression there is, based on above mentioned search.

Marc

1] A limit imposed by cognition: at most 4.

2] A nesting limit imposed by Excel's design: 7.

There are enough folks, not detered by any of the above, will happily tell you: chain them up to any number you want.

 

[Katherine Mason]

How does one "chain them up"? I have a need to have the following done to help in compiling demographic data. Here is the formula that I had wanted to use, but of course,I'm stopped at 7.

=IF(I2>64,"65+",IF(I2<=19,"0-19",IF(I2<=24,"20-24",IF(I2<=29,"25-29",IF(I2<=34,"30-34",IF(I2<=39,"35-39",IF(I2<=44,"40-44",IF(I2<=49,"45-49",IF(I2<=54,"50-54",IF(I2<=59,"55-59",IF(I2<=64,"60-64")))))))))))

Can someone tell me how I can get around the limit of 7?

 

[Aladin Akyurek]

How does one "chain them up"? I have a need to have the following done to help in compiling demographic data. Here is the formula that I had wanted to use, but of course,I'm stopped at 7.

=IF(I2>64,"65+",IF(I2<=19,"0-19",IF(I2<=24,"20-24",IF(I2<=29,"25-29",IF(I2<=34,"30-34",IF(I2<=39,"35-39",IF(I2<=44,"40-44",IF(I2<=49,"45-49",IF(I2<=54,"50-54",IF(I2<=59,"55-59",IF(I2<=64,"60-64")))))))))))

Can someone tell me how I can get around the limit of 7?

Since I'm not one of those folks who would suggest chaining them up, here is a formula that can replace your current IF-formula:

=VLOOKUP(I2,$E$1:$F$11,2)

Book1
	E	F	G	H	I
1	0	0-19
2	20	20-24		0-19	15
3	25	25-29		55-59	58
4	30	30-34		45-49	45
5	35	35-39		50-54	53
6	40	40-44		35-39	37
7	45	45-49		0-19	15
8	50	50-54		45-49	47
9	55	55-59		35-39	35
10	60	60-64		0-19	6
11	65	65+		35-39	39
Sheet1

 

[rrdonutz]

For Katherine, try:

=IF(I2>64,"65+",IF(I2<=19,"0-19",INT(I2/5)*5&"-"&INT(I2/5)*5+4))

--Tom

 

[Nimrod]

This solution has only 3 IFs ..... does it work for you ?
This solution ... if you took out the >65 and <19 would work all numbers from 10 to 99 in ranges from 0-4 and 5-9 :wink:

=IF(I2>64,"65+",IF(I2<=19,"0-19",IF(VALUE(RIGHT(I2))<5,LEFT(I2,1)&"0-"&LEFT(I2,1)&"4",LEFT(I2,1)&"5-"&LEFT(I2,1)&"9")))

 

[Scottcmu]

Having only skimmed this thread, it looks like you're trying to do a histogram. Excel will do this for you automatically... you'll need to go to Add Ins, add the Analysis Tool Pak, and then go to Data Analysis under the Tools menu and choose Histogram.