#value!

[birdland33]

Hi,

I'm trying to get a return in (H5) based on Column E multiplying by Column Q (100%, 130%, 150%). When I type the formulas individually they work, as IF(AND(E5>=O5,E5<p5,g5*q5) and="" so="" on="" for="" each="" of="" the="" other="" two="" returns.="" however,="" when="" i="" try="" to="" combine="" them="" get="" return="" #value !

I know it is probably something very simple that I am overlooking. The formula that I am using is:

=IF(AND(E5>=O5,E5
=O6,E5
=O7),G5*Q7,0))


[TABLE="width: 635"]
<tbody>[TR]
[TD] Results (E5)[/TD]
[TD] Potential (G5)[/TD]
[TD] Achieved[/TD]
[TD]Multipliers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] $ 25,500,000.00[/TD]
[TD] $ 2,808.00[/TD]
[TD="align: center"]#value ![/TD]
[TD]Sales[/TD]
[TD] (O5) 25,500,000.00[/TD]
[TD] (P5) 28,049,999.00[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] (O6) 28,050,000.00[/TD]
[TD] (P6) 30,854,999.00[/TD]
[TD="align: right"]130%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] (O7) 30,855,000.00[/TD]
[TD][/TD]
[TD="align: right"]150%[/TD]
[/TR]
</tbody>[/TABLE]


Any help on this is greatly appreciated.
Thanks!</p6,g5*q6),if(and(e5></p5),g5*q5),if(and(e5></p5,g5*q5)>

 

[LBinGA]

You need another IF in the statement.

Try this: =IF(AND(E5>=O5,E5),IF(O6>E5,O7),G5*Q7)

LBinGA

 

[Fluff]

Is this what you mean
=IF(AND(E5>=O5,E5< P5),G5*Q5,IF(AND(E5>=O6,E5< P6),G5*Q6,IF(E5>=O7,G5*Q7)))

 

[birdland33]

Is this what you mean
=IF(AND(E5>=O5,E5< P5),G5*Q5,IF(AND(E5>=O6,E5< P6),G5*Q6,IF(E5>=O7,G5*Q7)))

That's the one. Worked like a charm. Thank you very much. I see the error in my formula now!

 

[Fluff]

You're welcome & thanks for the feedback.

For future reference,when posting formulae that use either > or < make sure you put a space between the character & any text, otherwise your formula will get destroyed as in your OP.