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

Results (E5)	Potential (G5)	Achieved	Multipliers
$ 25,500,000.00	$ 2,808.00	#value !	Sales	(O5) 25,500,000.00	(P5) 28,049,999.00	100%
				(O6) 28,050,000.00	(P6) 30,854,999.00	130%
				(O7) 30,855,000.00		150%

<tbody>
</tbody>

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.