what formula do i use and how?...

saa38 · Mar 20th, 2002, 05:50 PM

I need to enter a formula that returns more than two possible results like the "IF" formula. For example if cell E2's value is 'A' then I need cell G2 to return a certain formula such as (F2*.35). If E2 is 'B' then I need G2 to return a different formula and so on. Up to 7 different possible values for cell E2. Hope this makes sense.
Thanks for any suggestions.

[ This Message was edited by: saa38 on 2002-03-20 16:59 ]

Jay Petrulis · Mar 20th, 2002, 06:08 PM

Hi,

You can nest up to 7 IF statements, although with some clever tricks, you can have more than 7.

You can also use Boolean algebra to overcome the 7 IF function limit and the formula might be easier to decipher.

Using CHOOSE is a good option as well.

The best way may be to use a lookup table and VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH.

Please post more details for a more specific answer.

Regards,
Jay

saa38 · Mar 20th, 2002, 07:26 PM

Thanks for the reply. How do you enter more than one IF function in a cell?

Jay Petrulis · Mar 20th, 2002, 07:43 PM

Hi,

With a bit from your post.

Most basic way using IF
=IF(E2="A",F2*0.35,IF(E2="B",F2*0.4,IF(E2="C",F2*0.5,0)))

Better way using IF
=F2*IF(E2="A",0.35,IF(E2="B",0.4,IF(E2="C",0.5,0)))

One Boolean way
=((E2="A")*0.35+(E2="B")*0.4+(E2="C")*0.5)*F2

This should get you started.

Regards,
Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 05:50 PM	#1
saa38 New Member Join Date: Mar 2002 Posts: 2	I need to enter a formula that returns more than two possible results like the "IF" formula. For example if cell E2's value is 'A' then I need cell G2 to return a certain formula such as (F2*.35). If E2 is 'B' then I need G2 to return a different formula and so on. Up to 7 different possible values for cell E2. Hope this makes sense. Thanks for any suggestions. [ This Message was edited by: saa38 on 2002-03-20 16:59 ]

Mar 20th, 2002, 06:08 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, You can nest up to 7 IF statements, although with some clever tricks, you can have more than 7. You can also use Boolean algebra to overcome the 7 IF function limit and the formula might be easier to decipher. Using CHOOSE is a good option as well. The best way may be to use a lookup table and VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH. Please post more details for a more specific answer. Regards, Jay

Mar 20th, 2002, 07:26 PM	#3
saa38 New Member Join Date: Mar 2002 Posts: 2	Thanks for the reply. How do you enter more than one IF function in a cell?

Mar 20th, 2002, 07:43 PM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, With a bit from your post. Most basic way using IF =IF(E2="A",F20.35,IF(E2="B",F20.4,IF(E2="C",F20.5,0))) Better way using IF =F2IF(E2="A",0.35,IF(E2="B",0.4,IF(E2="C",0.5,0))) One Boolean way =((E2="A")0.35+(E2="B")0.4+(E2="C")0.5)F2 This should get you started. Regards, Jay