How do I nest more then 7 IF's?

B

Bob Phillips

If you don't have Excel 2007, you can't. If you do have Excel 2007, you
shouldn't.

Tell us what you are trying to do, in words, there is bound to be a better
way.
 
M

M Kan

Are you trying to use this to catagorize a data set or summarize by several
criteria? As far as I know, you can't nest more than 7 IF statements.
 
K

KenCanuck

Essentially I have a row of 13 dollar figures that I need to do a formula
that says =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2... Is there another way to do
that?
 
K

KenCanuck

HI Bob, essentially I have a row of 13 dollar figures, and my formula goes
like this... =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2,... Another way to do
that where I don't nest 13 IF's?? Thanks
 
M

M Kan

I'd set up a VLOOKUP table that has your 13 values in one column (A) and the
corresponding values in the next column (B). I tend to group all of these on
a separate worksheet called LOOKUP values. This also let's you add more
values later. Assuming the first value you want to look up is in cell A1,
then your formula will look like this:

=VLOOKUP(A1,LOOKUP VALUES!A:B, 2, FALSE)
 
L

Lorne

KenCanuck said:
HI Bob, essentially I have a row of 13 dollar figures, and my formula goes
like this... =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2,... Another way to do
that where I don't nest 13 IF's?? Thanks

Look at the HLookup formula. using the range A1..J2 it will find a value in
row 1 and return the value from the same col in row 2.
 
R

RMarquez

I understand that there must be a better way, but I'm not sure how. I'll
explain to you what I'm trying to do, please let me know if you can help.

I have worksheet 1 where I enter 9 rows of data, 4 columns of info.
This data is translated into worksheet 2, which prioritizes the 9 rows based
on the 4 column criteria.
Now I'm on worksheet 3. I would like to pull the data from worksheet 2,
after it's been prioritized and relist in on worksheet 3 in order of
importance 1 through 9.

But I can only nest 7 IF's, not 9. So IF any of worksheet 2 cells a1-a9
have a "1" in it, enter the corresponding worksheet-2, column-B data into
Worksheet 3's B1 cell.

Is this clear? Please let me know if you can help. Thanks!
 
D

David Biddulph

=INDEX(Sheet2!B$1:B$9,MATCH(ROW(),Sheet1!A$1:A$9,0)) in sheet 3 B1, & copy
down ?
 
J

JB

I have a sheet where I need to asign points based upon a finish of a race.
The race finsh place # will be in one colum and I want a formula to
automatically figure the points based upon finish. I was using an IF formula
but I need this to go down up to 20 places and it will not. Can you help me??
 
R

Roger Govier

Hi

On say sheet2, enter 1 to 20 in A1:A20
Enter the points associated with each position in B1:B20
On the sheet where you need to assign the points, assuming it is A1 on
Sheet1, enter in B1
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
Copy down as required
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top