Nested If problems

B

BEEJAY

Greetings:
Have the following, (used on multiple lines, in multiple workbooks)
Works great
=IF(D6="Alum",130,IF(D6="Rubber",145,0))

Need to expand it as follows:

=IF($E$5=0
then (D6="Alum",130,IF(D6="Rubber",145,0))
=IF($E$5=1
then =IF(D6="Alum",60,IF(D6="Rubber",70,0))

Have spent countless hours trying to "join" the above
- playing with the brackets and moving the Zero around, etc....
I'm STUCK.
I'm positive this should work if properly bracketted.
Due to the "application", I'd rather not change this to a look-up chart.
Thank-you
 
A

arno

Hi,

I am not going to solve your problem :)

You'd understand how to write complex formulas. In your case start
with:

=IF(A1=1, 1, 2)

this is rather stupid but a correct formula, now you replace the "1"
representing the "True"-part of the condition with whatever formula you
like, make sure you do not cross the comma, nothing else.

=IF(A1=1, IF(B1=1, C1, D1) , 2)

right? still, the part "2" of the first condition is missing, this is
the false-part. just replace 2 with your formula, make sure to not
cross the ")" at the end.

=IF(A1=1, IF(B1=1, C1, D1) , IF(X1=1, Y1, Z1) )

ok. so what next? instead of C1 you need a vlookup? no worries:

=IF(A1=1, IF(B1=1, vlookup(m1, myrange, 3, false), D1) ,
IF(X1=1, Y1, Z1) )

etc. etc. etc.

arno
 
B

Bearacade

=IF($E$5=0, IF(D6="Alum",130,IF(D6="Rubber",145,0)), IF($E$5=1,
IF(D6="Alum",60,IF(D6="Rubber",70,0))))
 
C

CLR

Another way..........

=IF(E5=0,LOOKUP(D6,{"alum","rubber"},{130,145}),IF(E5=1,LOOKUP(D6,{"alum","rubber"},{60,70})))

Vaya con Dios,
Chuck, CABGx3
 
B

BEEJAY

Thanks all for your input.
I really was wanting to stay away from any type of look-up.
The response by Bearacade is right on the nose.
If I hadn't over-complicated things with a gross excess of brackets,
I might have got there........ eventually.

Thanks every so much.
 
B

BEEJAY

Greetings:
CLR: After a restless nite, I figured I better have another look at your
post.
My first quick view, I thought I have to make a standard lookup chart, which
I was hoping to stay away from.
A second look (careful this time), plus a trial run, showed me I was wrong.
The potential for your sample is EXCITING!!
(I know, I'm weird, but that's my problem).
Thanks for the input.
 
C

CLR

No problem BEEJAY, I'm just glad you got something workinhg for you. As you
know, there are usually several ways to do something in Excel, and the "best"
way is the one that the user feels most comfortable with. (and not
necessarily the one that's the technical "best")..... And, sometimes, by
getting multiple responses to the same question, we see answers to problems
we didn't even know we had.....or something that will help us with the next
version of whatever we're doing. All in all, these newsgroups are a
wonderful place to come........I help whenever I can, but I'm right back here
when I have a problem also. Keep coming back, and thanks for the feedback.


Vaya con Dios,
Chuck, CABGx3
 
B

Bearacade

CLR said:
No problem BEEJAY, I'm just glad you got something workinhg for you. As
you
know, there are usually several ways to do something in Excel, and the
"best"
way is the one that the user feels most comfortable with. (and not
necessarily the one that's the technical "best")..... And, sometimes,
by
getting multiple responses to the same question, we see answers to
problems
we didn't even know we had.....or something that will help us with the
next
version of whatever we're doing. All in all, these newsgroups are a
wonderful place to come........I help whenever I can, but I'm right
back here
when I have a problem also. Keep coming back, and thanks for the
feedback.


Vaya con Dios,
Chuck, CABGx3
Click to expand...

I couldn't agree more. I have learn so much helping others. I find
that people are ususally comfortable with a handful of function and we
tend to try and solve all problems with these functions. I am a big
(often bullheadedly) fan of nested if statement. But I am learning
from many examples that lookup is a much "cleaner" approach most of the
time.

Thanks CLR
 
Top