Using an IF to combine multiple formulas

S

sharkfoot

How do I write an if formula that does the following:


If D35 is between 0% and 105.00% then return the value of this
formula:
=INDEX('setf-lease'!$W$5:$AE$24,
MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,),
MATCH(D25,'setf-lease'!$W$5:$AE$5,))

If D35 is between 105.01% and 110.00% then return the value of this
formula:
=INDEX('setf-lease'!$W$27:$AE$46,
MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,),
MATCH(D25,'setf-lease'!$W$27:$AE$27,))

If D35 is greater than 110.00% then return the value "Exceeds
Guidlines"

So I'm trying to combine more than one formula and do not know how to
do that. Can anyone assist? Thanks!
 
S

sharkfoot

This is what I tried but it doesn't work. Can someone tell me what the
problem is?


Code:
--------------------
IF(D35>110%,"Exceeds Guidelines",IF(D35<110%,=INDEX('setf-lease'!$W$5:$AE$24, MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,), MATCH(D25,'setf-lease'!$W$5:$AE$5,),IF(D35<105%,=INDEX('setf-lease'!$W$27:$AE$46, MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,), MATCH(D25,'setf-lease'!$W$27:$AE$27,))))
--------------------


This is driving me crazy.

EDIT

I guess I can post this the regular way since it's so wrong the code
thingy doesn't even pick it up:

IF(D35>110%,"Exceeds
Guidelines",IF(D35<110%,=INDEX('setf-lease'!$W$5:$AE$24,
MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,),
MATCH(D25,'setf-lease'!$W$5:$AE$5,)),IF(D35<105%,=INDEX('setf-lease'!$W$27:$AE$46,
MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,),
MATCH(D25,'setf-lease'!$W$27:$AE$27,))
 
C

Clivey_UK

I would say you should use the following formula type:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,"B","A")).
Where "A" represents your first condition, "B" your second, and
anything over 1.1 (110%) gets 'Exceeds Guidelines.
Therefore the full formula should be:
=IF(D35>1.1,"Exceeds
Guidelines",IF(D35>1.05,INDEX('setf-lease'!$W$27:$AE$46,
MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,),
MATCH(D25,'setf-lease'!$W$27:$AE$27,)),INDEX('setf-lease'!$W$5:$AE$24,
MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,),
MATCH(D25,'setf-lease'!$W$5:$AE$5,))))
I can't test out without the values, but I've simply pasted in your
conditions in place of A and B.
Clive
 
D

Domenic

Try...

=IF(D35<=110%,VLOOKUP('TNT'!O4,IF(D35<=105%,'setf-lease'!$W$5:$AE$24,'set
f-lease'!$W$27:$AE$46),MATCH(D25,IF(D35<=105%,'setf-lease'!$W$5:$AE$5,'se
tf-lease'!$W$27:$AE$27),0),0),"Exceeds Guidelines")

Hope this helps!
 
S

sharkfoot

Clivey_UK said:
I would say you should use the following formula type:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,"B","A")).
Where "A" represents your first condition, "B" your second, an
anything over 1.1 (110%) gets 'Exceeds Guidelines.
Therefore the full formula should be:
=IF(D35>1.1,"Exceed
Guidelines",IF(D35>1.05,INDEX('setf-lease'!$W$27:$AE$46
MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,)
MATCH(D25,'setf-lease'!$W$27:$AE$27,)),INDEX('setf-lease'!$W$5:$AE$24
MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,)
MATCH(D25,'setf-lease'!$W$5:$AE$5,))))
I can't test out without the values, but I've simply pasted in you
conditions in place of A and B.
Clive
Click to expand...

That did it. My brain starts turning to mush after a couple of hours o
tinkering with the same formula. I appreciate your assistance and than
you
 
C

Clivey_UK

Domenic's response looks like it might be a neater formula. Give it
try.
I find the best way to do complex formulas (I also get the mus
problem!) is to build it up in different cells. You'd already create
the most complex part so keep that to one side.
Then in another cell (say A2 for sake of argument), do a simple I
formula for just 2 of the conditions. e.g.
=IF(D35>105%,"A","B")
Then in another cell (say B2) write the next part, i.e.
=IF(D35>110%,"Exceeds Guidelines",A2).
Check both formulas work. Now combine them by copyin
IF(D35>105%,"A","B") from A2 and pasting it to where it says A2 in th
second formula, giving you
IF(D35>110%,"Exceeds Guidelines",IF(D35>105%,"A","B"))
AFter checking the combined formula works, replace "A" and "B" with th
bits of the formula you'd already worked out.
It's important to drop the initial '=' when you copy the formulas int
another formula.
Clive
 
Top