Nested logic arguments- internal reference?

[Rich D.]

Greetings-

I have a basic understanding of Excel, but I am not a logic wiz...

I am attempting to create a formula that will return a numeric valu
for one of two different calculations nested within the formula
depending on whether criteria are met. In other words, I only want th
results of second calculation to display if the specified criteria i
the first calculation are FALSE. If the specified criteria in the firs
calculation are met, I would like the results of that calculation t
display.

Part One of the would-be formula works fine as a stand-alone, an
appears like this:

=IF(I95>=K95,L95>=I95*$N$82)*($N$77/I95*(I95*$N$83))

Part Two of the would-be formula works fine as a stand-alone, an
appears like this:

=IF(I95>=K95,L95<I95*$N$82)*($N$77/I95*E10)-$N$77

Can I embed these two arguments within the same formula, triggerin
Part Two only if the conditions contained in the first half of Part On
due not trigger Part One?

Is this a syntax issue, or am I attempting to ask Excel to perform i
one formula what it needs multiple formulae to accomplish?

Thank you for any insight,

Ric

 

[Peo Sjoblom]

Try

=IF(I95>=K95,(L95>=I95*$N$82)*($N$77/I95*(I95*$N$83)),(L95<I95*$N$82)*($N$77
/I95*E10)-$N$77)

 

[Rich D.]

Peo-

Thank you for your kind suggestion. Your formula displays the value fo
Part One, when those specific criteria are met (which solved part o
the problem I was having), but displays "0" values when applied t
cells where Part One does not apply and part Two should be calculated.

Your suggestion was
=IF(I95>=K95,(L95>=I95*$N$82)*($N$77/I95*(I95*$N$83)),(L95<I95*$N$82)*($N$77/I95*E10)-$N$77)


Looking at the original formulas I am trying to nest:

Part One
=IF(I95>=K95,L95>=I95*$N$82)*($N$77/I95*(I95*$N$83))

and, Part Two
=IF(I95>=K95,L95<I95*$N$82)*($N$77/I95*E10)-$N$77

What I'm trying to express in a compound formula, is that when I95 i
equal to or greater than K95 and L95 is equal to or greater than I9
multiplied by the value in cell $N$82, Excel should perform and displa
the calculation in the second half of Part One.

BUT, when I95 is equal to or greater than K95 and L95 is LESS than I9
multiplied by the value in cell $N$82, Excel should perform and displa
the calculation in the second half of Part Two.

I hope I haven't complicated things.

Thanks again for any additional insight,

Ric

 

[Ken Wright]

Perhaps this?

=IF(AND(I95>=K95,L95>=(I95*$N$82)),Formula1,IF(AND(I95>=K95,L95<(I95*$N$82)),For
mula2,0))

Just replace Formula1 and Formula2 with whatever you want it to do. If it
doesn't meet either criteria it will return 0 as you have not specified what
happens if I95 is less than K95.

 

[Peo Sjoblom]

=IF(AND(I95>=K95,L95>=I95*$N$82),($N$77/I95*(I95*$N$83)),IF(AND(I95>=K95,L95
<I95*$N$82),$N$77/I95*E10-$N$77,"No Condition True"))

 

[Rich D.]

Many thanks to Peo and Ken.

Problem solved! Your insight has provided me with a greate
understanding of nested logic formulae in Excel.

Best Regards,

Ric

 

[Ken Wright]

Just for the record, if you ever build a formula that starts using mulitple IFs
and you think you will run out, you are probably using the wrong function. In
these cases, the aim is often better served using VLOOKUP, LOOKUP, HLOOKUP,
INDEX/MATCH, MATCH/OFFSET etc

 

[Rich D.]

Thanks for the pointers, Ken. Hopefully, I'll remember them if the tim
comes for more complex problems. Now, if only you could tell me where
left my car keys...


Ric

 

[Ken Wright]

LOL - That's one I can't crack, even for myself!!!!!!!!!

 

[Peo Sjoblom]

Same place where you left the TV remote control? <g>

--

Regards,

Peo Sjoblom

LOL - That's one I can't crack, even for myself!!!!!!!!!

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

 

[Ken Wright]

Ahhhhh - Clarification is obviously needed here. If my things were left where I
put them, there would be no problem, BUT, try telling my wife NOT to move my
things, and NOT to *tidy up* the remote control control, or my keys, or my mail,
or...................... (runs out of space)!!!!!!!!

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Same place where you left the TV remote control? <g>