Nested If problems

[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

[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

[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))))

[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



"BEEJAY" wrote:

> 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
>

[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.


"CLR" wrote:

> 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
>
>
>
> "BEEJAY" wrote:
>
> > 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
> >

[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.


"CLR" wrote:

> 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
>
>
>
> "BEEJAY" wrote:
>
> > 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
> >

[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



"BEEJAY" wrote:

> 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.
>
>
> "CLR" wrote:
>
> > 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
> >
> >
> >
> > "BEEJAY" wrote:
> >
> > > 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
> > >

[Bearacade]

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

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