IF formula problem..please help

[DEATHLINX]

hello my name is skylar and i am trying to use the following formula in a cell. it is comparing height vs weight standards of individuals.

=IF(E2=N2,F2-O2,IF(E2=N3,F2-O3,IF(E2=N4,F2-O4,IF(E2=N5,F2-O5,IF(E2=N6,F2-O6,IF(E2=N7,F2-O7,IF(E2=N8,F2-O8,IF(E2=N9,F2-O9,IF(E2=N10,F2-O10,IF(E2=N11,F2-O11,IF(E2=N12,F2-O12,IF(E2=N13,F2-O13,IF(E2=N14,F2-O14,IF(E2=N15,F2-O15,IF(E2=N16,F2-O16,IF(E2=N17,F2-O17,IF(E2=N18,F2-O18,IF(E2=N19,F2-O19,IF(E2=N20,F2-O20,IF(E2=N21,F2-O21,IF(E2=N22,F2-O22,IF(E2=N23,F2-O23,IF(E2=N24,F2-O24,IF(E2=N25,F2-O25,IF(E2=N26,F2-O26,IF(E2=N27,F2-O27,IF(E2=N28,F2-O28,IF(E2=N29,F2-O29,IF(E2=N30,F2-O30,IF(E2=N31,F2-O31,IF(E2=N32,F2-O32,IF(E2=N33,F2-O33,IF(E2=N34,F2-O34,IF(E2=N35,F2-O35))))))))))))))))))))))))))))))))))

that is the formula, by all means it should workm but after the 8th if in the formula it tells me there is an error and will not let me put it in the cell. is there a maximum amount of IF's u can have in one cell? i really need help with this. it is highly important for this to work

thank you

 

[seenfresh]

What version of Excel/Office are you running?

 

[DEATHLINX]

i am using Office Excel 2003 SP3

 

[ClimoC]

You cannot nest more than 7 conditions for an If statement. You need to use a macro, either a whole heap of if statements, or a Select Case statement.

Im not doing anything at work atm so list all the conditions and I'll write you a code. Don't forget to mention which cell range the 'formula' is to go in.

C

 

[lenze]

Hi and welccome to the board!!
Maybe just

=F2-VLOOKUP(E2,$N$2:$O$35,2,0)

lenze

 

[DEATHLINX]

lenze,

yea that just confused me..i have no idea what that is..but it worked..

i thank u so much...exactly wut i needed

 

[lenze]

Have a look at VLOOKUP in the help file. Your problem is simply this!
1.Look in Column "N" (N2:N35)for a match with cell E2.
2.Take the Value in the same row from Column "O" (O2:O35) and subtract it from Cell F2.
That's what my formula does. Just try it.

lenze

 

[ClimoC]

That's a lookup. Since what you're testing E2 against (eg N8) has its answer next to it (O8), then it finds E2 in the N column, and returns the value from the column over in the same row (O8). Then it subtracts that (O8) from F2.

Gotta almost read it backwards.

 

[DEATHLINX]

thank you all!! especially lenze!! u all helped out alot

skylar

 

[seenfresh]

Another possibility is:

=SUM(IF(E2=N2:N35,F2-O2:O35))

Confirm with CTR+SHIFT+ENTER