Help with IF

Ladytanj · May 19th, 2002, 11:11 AM

I have a spreadsheet and have the following formula that works just fine: =IF(I2>=8,IF(C3=1,6,IF(C3=2,5,IF(C3=3,4,IF(C3=4,3,IF(C3=5,2,IF(C3=6,1,)))))))
My problem is I want to add some veriables to the formula and I can't seem to get them in the right order. I want to add the variable IF I2=7, then when I have cell c3=1,5,IF(c3=2,4,IF etc. and I need to do this all the way from I2=6 then if c3=1,4 and so on. Is this two complicated or should I explain it better. Basically if cell I2 is one number them when I enter a number in c3 the value of my active cell should change. I hope I didn't just make this harder then it has to be.
Deb

Yogi Anand · May 19th, 2002, 11:41 AM

The way you are using the IF function, you will hit the maximum of 7 levels of nesting for a function. You ought to consider using other functions such as INDEX, MATCH, VLOOKUP, ...

Regards!

Aladin Akyurek · May 19th, 2002, 11:51 AM

Quote:

On 2002-05-19 10:11, Ladytanj wrote:
I have a spreadsheet and have the following formula that works just fine: =IF(I2>=8,IF(C3=1,6,IF(C3=2,5,IF(C3=3,4,IF(C3=4,3,IF(C3=5,2,IF(C3=6,1,)))))))
My problem is I want to add some veriables to the formula and I can't seem to get them in the right order. I want to add the variable IF I2=7, then when I have cell c3=1,5,IF(c3=2,4,IF etc. and I need to do this all the way from I2=6 then if c3=1,4 and so on. Is this two complicated or should I explain it better. Basically if cell I2 is one number them when I enter a number in c3 the value of my active cell should change. I hope I didn't just make this harder then it has to be.
Deb

Deb,

Your problem can be solved more efficiently by converting the long chain of IF most likely into a VLOOKUP formula. Care to enumarate all of the conditions that you use? If there is a regular pattern to it, you might as well make that explicit.

Just a shot at. Is it

=If(i2>=8,8,i2)-(c3+1)

you're looking for?

Aladin

[ This Message was edited by: aladin akyurek on 2002-05-19 12:26 ]

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

May 19th, 2002, 11:11 AM	#1
Ladytanj New Member Join Date: May 2002 Location: Deb Posts: 1	I have a spreadsheet and have the following formula that works just fine: =IF(I2>=8,IF(C3=1,6,IF(C3=2,5,IF(C3=3,4,IF(C3=4,3,IF(C3=5,2,IF(C3=6,1,))))))) My problem is I want to add some veriables to the formula and I can't seem to get them in the right order. I want to add the variable IF I2=7, then when I have cell c3=1,5,IF(c3=2,4,IF etc. and I need to do this all the way from I2=6 then if c3=1,4 and so on. Is this two complicated or should I explain it better. Basically if cell I2 is one number them when I enter a number in c3 the value of my active cell should change. I hope I didn't just make this harder then it has to be. Deb

May 19th, 2002, 11:41 AM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	The way you are using the IF function, you will hit the maximum of 7 levels of nesting for a function. You ought to consider using other functions such as INDEX, MATCH, VLOOKUP, ... Regards!