Anyway to nest more than 7 "IF"s?

  • Thread starter Thread starter jayhawk1919
  • Start date Start date
J

jayhawk1919

This is the forumla I'm trying to write, but it won't work. I suspect
it has to do with trying to nest more than 7 "IF"s. Is there any way
to nest more, or to write it in a different way so I don't have to nest
more than 7?

This is what I wanna write:

=IF(INDIRECT("F"&"1021")="Yes,
DT",E516*0.8,IF(INDIRECT("F"&"1021")="No,
DT",E516*1.25,IF(INDIRECT("F"&"1021")="Yes,
DD",E516*0.8,IF(INDIRECT("F"&"1021")="No,
DD",E516*1.5,IF(INDIRECT("F"&"1021")="Yes,
CS",E516*0.8,IF(INDIRECT("F"&"1021")="No,
CS",E516*1.1,IF(INDIRECT("F"&"1021")="Yes,
FB",E516*0.8,IF(INDIRECT("F"&"1021")="No,
FB",E516*1.1,IF(INDIRECT("F"&"1021")="Motion",E516*0.9,"Blah")))))))
 
I don't know if I haven't had enough coffee, but I don't see why you're
using INDIRECT("F"&"1021"). It's always that cell, so wouldn't F1021 work?
If you're only examining F1021, use a VLOOKUP to a table containing the
various expressions.

Yes, DT E516*0.8
No, DT E516*1.25
..
..
Motion

etc. This table can be as long as necessary.

Now use:

VLOOKUP(F1021, Table, 2, False)

Alternately, the table:
Yes, DT 0.8
No, DT" 1.25
etc.
The formula:
VLOOKUP(F1021, Table, 2, False)*E516

Earl Kiosterud
mvpearl omitthisword at verizon period net
 
Heh, it's not your coffee. The reason I used INDIRECT was because I
eventually want to reproduce it into many cells, but I didn't want
Excel to change F1021 for the different cells as it would if it was not
in the indirect.

But I'm not sure I understand your second method. Please explain.

Thanks for your help.
 
Did you ever hear of absolute and relative references ?
Add $ to cell addresses and they don't change while copying.

Anyway, I would use Lookup as Earl suggested, but here's an IF formula that
uses only 5.

=IF(OR($F$1021={"YES,DT","YES,DD","YES,CS","YES,FB"}),E516*0.8,IF(OR($F$1021
={"NO,CS","NO,FB"}),E516*1.1,IF($F$1021="NO,DT",E516*1.25,IF($F$1021="NO,DD"
,E516*1.5,IF($F$1021="MOTION",E516*0.9,"Blah")))))

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Thanks a bunch. I'm kinda new to the world of Excel, so I appreciate
all the help I can get. So no, I didn't know about putting $ in front
of the cell reference. So thanks for that too!
 
Thanks so much for all your help.

Now what I want to do with that is stick it in to this equation:

=IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Superstar",IF(OR($F$1021={"Yes,
DT","Yes, DD","Yes, CS","Yes, FB"}),E516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),E516*1.1,IF($F$1021="No,
DT",E516*1.25,IF($F$1021="No,
DD",E516*1.5,IF($F$1021="Motion",E516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Big
Man",IF(OR($F$1021={"Yes, DT","Yes, DD","Yes, CS","Yes,
FB"}),F516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),F516*1.1,IF($F$1021="No,
DT",F516*1.25,IF($F$1021="No,
DD",F516*1.5,IF($F$1021="Motion",F516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Shootout",IF(OR($F$1021={"Yes,
DT","Yes, DD","Yes, CS","Yes, FB"}),G516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),G516*1.1,IF($F$1021="No,
DT",G516*1.25,IF($F$1021="No,
DD",G516*1.5,IF($F$1021="Motion",G516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Fast
Break",*H516*,IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Motion
PF",*I516*,IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Motion
Center",*J516*,"Blah"))))))


See here I already implemented it in the first couple, adjusting the
column depending. What I'm saying is this: If they have "Superstar"
it should take the values from column E, and then formats them
depending on the other things, which we determined. However if they
have "Big Man" I want it to format the values from column F, not column
E. So I was gonna stick what we have into the formula I already had
(which determined which column to use). But now it tells me the
formula is too long.

So if you could please help again, I would really appreciate it.

(The bold things in the formula are the ones I want to replace with the
formula we derived earlier, but it wouldn't let me.)
 
Talk about sneaking up on a guy with a sucker punch !!!

Earl's suggestion of using a lookup formula will help make this whole thing
a lot shorter.
 
This is what Earl was talking about.
Read this and incorporate it into your formula and see what happens.

You can make a table of all your choices and values, and then have your
formula lookup the choice and then apply the corresponding value.
COL A COL B
1 YES,DT 0.8
2 NO,DT 1.25
3 YES,DD 0.8
4 NO,DD 1.5
5 YES,CS 0.8
6 NO,CS 1.1
7 YES,FB 0.8
8 NO,FB 1.1
9 MOTION 0.9

Say the choices are in ColumnA and the values are in ColumnB, starting in
Row1.

So your list can be described as A1:B9
OR, you can give it a name as Earl did and call it "Table".
I prefer addresses, so it can be understood at a later time.

Now your formula will "lookup" the choice in F1021:
=Vlookup(F1021,
From this rangeA1:B9
=Vlookup(F1021,A1:B9,
And, if it finds a match, return the value in the same row, *second* column
of the list,
=Vlookup(F1021,A1:B9,2
And you tell the formula that you want an exact match by using zero
=Vlookup(F1021,A1:B9,2,0)

Now that you have found the value, you multiply it by the contents of the
pre-determined cell E516
=Vlookup(F1021,A1:B9,2,0)*E516

You can now correct your references so that F1021 doesn't change when you
copy it down:
=Vlookup($F$1021,A1:B9,2,0)*E516

BUT, you also don't want the addresses of your list to change as you copy,
so change that also:
=Vlookup($F$1021,$A$1:$B$9,2,0)*E516

I don't know what you wish to happen to E516.
You can make it either relative, as it is now, without the dollar signs,
which means it will change with copying, OR make it absolute as was done
with the others, by adding the dollar signs.

If no choice is matched to the list, you will see an error returned #N/A.

With your new knowledge, eliminate those Indirects to make it shorter yet.

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

RagDyer said:
Talk about sneaking up on a guy with a sucker punch !!!

Earl's suggestion of using a lookup formula will help make this whole thing
a lot shorter.

jayhawk1919 said:
Thanks so much for all your help.

Now what I want to do with that is stick it in to this equation:

=IF(INDIRECT("G"&INDIRECT("D"&"1021"))="Superstar",IF(OR($F$1021={"Yes,
DT","Yes, DD","Yes, CS","Yes, FB"}),E516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),E516*1.1,IF($F$1021="No,
DT",E516*1.25,IF($F$1021="No,
Click to expand...
DD",E516*1.5,IF($F$1021="Motion",E516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRE
CT("D"&"1021"))="Big
Man",IF(OR($F$1021={"Yes, DT","Yes, DD","Yes, CS","Yes,
FB"}),F516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),F516*1.1,IF($F$1021="No,
DT",F516*1.25,IF($F$1021="No,
Click to expand...
DD",F516*1.5,IF($F$1021="Motion",F516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRE
CT("D"&"1021"))="Shootout",IF(OR($F$1021={"Yes,
DT","Yes, DD","Yes, CS","Yes, FB"}),G516*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),G516*1.1,IF($F$1021="No,
DT",G516*1.25,IF($F$1021="No,
Click to expand...
DD",G516*1.5,IF($F$1021="Motion",G516*0.9,"Blah"))))),IF(INDIRECT("G"&INDIRE
Click to expand...
 
Ha, sorry for that, but once again, thanks for the help.

So how does VLOOKUP work, and what would I have to do to my sheet to
implement that?

I read the Excel summary in the functions list, and I understand how it
works, but I don't understand how I would have to format the
information that the VLOOKUP would refer to.
 
Thanks a bunch for helping me, I think I figured out a way to do it.
Just one more question: You showed me how you can type $ to make sure
the cell doesn't change. What if I need to use an INDIRECT reference
for it to work, but I still want the number to change?

Here's my example:

What I decided to do was have a third cell, that depending on the
strategy output the column, such as "E" or "H". Then I did:

=IF(OR($F$1021={"Yes, DT","Yes, DD","Yes, CS","Yes,
FB"}),INDIRECT(F1044&516)*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),INDIRECT(F1044&516)*1.1,IF($F$1021="No,
DT",INDIRECT(F1044&516)*1.25,IF($F$1021="No,
DD",E516*1.5,IF($F$1021="Motion",INDIRECT(F1044&516)*0.9,"Blah")))))

Where F1044 is the place where the column is output.
But I want the 516 to change to 517 when I paste it in the cell below
it. How would I do that?
 
Jay,

If you put it in quotes, it's text, and will never be adjusted when you
copy, $ or no $.
=INDIRECT("A1")

If you don't use quotes, it will (or won't) if there are no $ (or are). But
this means to look in cell A1 for a cell address, where the first means look
in A1 for an address, and retrieve what's at that address.
=INDIRECT(A1)
 
I see that you've decided not to use the lookup procedure (unwise).

I don't understand what you're trying to accomplish with
Indirect(F1044&516)
Which doesn't work !

When dealing with unfamiliar functions and procedures, I find it easier to
work from the inside out, making sure that everything is in the correct
syntax and that what I want to happen *can* happen.
I say *can* ... as against *will*, because there are many under lying
factors that may prevent a possible action from occurring, even though your
concept is correct.

For example:
=A1*B1
CAN work
BUT, if either cell contains text, it
WILL NOT work, returning the
#VALUE! error.

When you receive an error message, you want to be sure that it's *not*
because the function was presented incorrectly.

You are trying to do something with:
INDIRECT(F1044&516)*0.8

So, test it by placing it by itself in a cell and see what happens.
Enter the formula in an empty cell,
=INDIRECT(F1044&516)*0.8
And enter 2 values in F1044 and F516.
What happened ?

I still don't understand what you are trying to do.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




Thanks a bunch for helping me, I think I figured out a way to do it.
Just one more question: You showed me how you can type $ to make sure
the cell doesn't change. What if I need to use an INDIRECT reference
for it to work, but I still want the number to change?

Here's my example:

What I decided to do was have a third cell, that depending on the
strategy output the column, such as "E" or "H". Then I did:

=IF(OR($F$1021={"Yes, DT","Yes, DD","Yes, CS","Yes,
FB"}),INDIRECT(F1044&516)*0.8,IF(OR($F$1021
={"No, CS","No, FB"}),INDIRECT(F1044&516)*1.1,IF($F$1021="No,
DT",INDIRECT(F1044&516)*1.25,IF($F$1021="No,
DD",E516*1.5,IF($F$1021="Motion",INDIRECT(F1044&516)*0.9,"Blah")))))

Where F1044 is the place where the column is output.
But I want the 516 to change to 517 when I paste it in the cell below
it. How would I do that?
 
You'll find information about VLOOKUP in Excel Help. If you have more questions after reading
that, post back.
 
RD,

What I'm doing is putting the column (either E, F, G, H, I or J) in
cell F1044. Then the INDIRECT(F1044&516) looks in F1044 and finds the
column, puts it together with 516, and then finds that value. For
example, lets say the whole IF() function outputs "H" in F1044 then it
would read H516 and take the value from there, which is exactly what I
want. It works perfectly. The only problem I'm experiencing is that
when I go to copy it, I want the 516 to change to 517 when I paste it
in the next cell down. Just as if I were to put E516 there, and then
when I pasted it in the cell below it would say E517. It's looking at
a part in the spreadsheet starting with 516 and going down to 521. But
I have to do this alot, so I was wondering if I could just copy and
paste and get the 516 to turn to 517 when I paste it in the cell
below.

Thanks for your help...
 
When you copy down, the F1044 increments to F1045 - F1046 - ... etc.,
doesn't it ?
Is that what you want ?
Do you want both addresses to increase as you copy down ?

Anyway, to get your 516 to increment, use the Row() function.
Depending on where (which row) you're starting your formula, you could try
something like this:
=Indirect(F1044&Row()+50)
Where the first row that contained your formula before copying down was Row
466.

The Row() function looks at the row it's in - Row466 - and adds the 50,
giving you the original 516.
Copying down to the next row, Row() sees row 467, and adds 50 to that, to
give you 517.

You can use any number, with a plus OR a minus to attain your starting row
number, and then go from there.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Ok.

You can use CHOICE. But here in Brazil, there is a formula called
ESCOLHER. It's very simple. Just see on Menu-help.

There is a limit on nº of IF's. The limit are 7.
 
Ok.

You can use CHOICE. But here in Brazil, there is a formula called
ESCOLHER. It's very simple. Just see on Menu-help.

There is a limit on nº of IF's. The limit are 7.
 
Yeah it will if you do

=IF(ISERROR(A1*B1),"",(A1*B1))

:D

For example:
=A1*B1
CAN work
BUT, if either cell contains text, it
WILL NOT work, returning the
#VALUE! error.
;)
 
You could also hard-code the VLOOKUP table. For example:

=VLOOKUP(A26,{"YES,DT",0.8;"NO,DT",1.25;"YES,DD",0.8;"NO,DD",1.5;"YES,CS",0.8;"NO,CS",1.1;"YES,FB",0.8;"NO,FB",1.1;"MOTION",0.9},2,FALSE)
 
Back
Top