MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Too Many IF Statements in one formula problem?


Posted by Sun Shine on June 09, 2000 3:35 PM


I am having a problem with IF statements. Are you only allowed to have so many IF statements in the same formula? If you’re only allowed so many how can you get around the problem?

Here is my problem: I want to enter various alpha-numeric coordinates (to a maximum of 15 possibilities) into a cell and have a value appear in another cell based on the particular alpha-numeric coordinate that I enter into the cell.

For example, I want to be able to enter “B6” into cell A4 and have a value of 100 to appear in cell E2, and if say I entered “D4” into cell A4 then I want a value of 200 to appear in cell E2, and if say I entered “H6” into cell A4 then I want a value of 300 to appear in cell E2, and so on, up to about 15 possible combinations. So I tried the following formula entered in cell E2:

=IF(A4=”B6”,”100”,IF(A4=”D4”,”200”,IF(A4=”H6”,”300”)))

This formula works fine for up to about 6 or 7 IFs, but after about 7 possibilities it says “error in formula”. Does anyone know how I could put more than 7 IF statements in one formula? Or solve my problem some other way?

Thank you.


Posted by Jaime on June 10, 0100 11:39 AM

or better yet use boolean logic.

example

your formula would read:

=100*(A1=B1)+200*(A1=D1)+300*(A1=H1)+400*(A1=J1)

THIS ELIMINATES THE SEVEN NESTED IF PROBLEM.

Posted by Jakamaki on June 09, 0100 4:07 PM

If function accepts only 7 arguments. To work around this limit, use as 7th argument a reference to a cell nearby and keep building using 7 more arguments; if you need more than 14 simmilary for the 14th argument make a reference to another cell in which you'll continue building your nested statement. It's tidious (probably there is another way) but I'm not aware of onther solution for worksheet formula (VBA can do easier).
start the formula in E2
=IF($A$4=B6,100,IF($A$4=D4,200,IF($A$4=H6,300,IF($A$4=J4,400,IF($A$4=L6,500,IF($A$4=M4,600,IF($A$4=N6,700,F2))))))) 7th argument F2 where you'll have

=IF($A$4=P6,800,IF($A$4=Q4,900,IF($A$4=R6,1000,IF($A$4=S4,1100,IF($A$4=T6,1200,IF($A$4=U4,1200,IF($A$4=V6,1300,G2))))))).

Cheers