Formula Help

[bluenose5709]

Hi,

I am banging my head tying to figure out what many of you may see as a simple formula. I am hoping someone may help me and prevent an increasing headache.

So I have a simple Worksheet and I wish for a calculation to be made based upon a figure I populate for example:

If the figure in D5 is >500 but <1001 the calculate D5*E9
If the figure in D5 is >1000 but <2001 the calculate D5*E10
If the figure in D5 is >2000 but <3001 the calculate D5*E11

This is something i have done before but i am having a brain fog and just need someone to re-educate me.

Thank you all in anticipation

Bev

 

[Scott Huish]

What do you want to do return if it is outside those values?
But right now I have this:
=D5*OFFSET(E9,MATCH(D5,{500,1001,2001},1)-1,0)

 

[Fluff]

What should happen if D5 is <=500 or >=3001

 

[bluenose5709]

All, This works perfectly. Thank you.

To answer your questions, i would prefer that there was either a Zero returned if outside of the prescribed values or indeed a nice message informing the user to "Speak to BOB"

thank you again

regards
Bev

 

[Fluff]

How about

=IF(OR(D5<=500,D5>=3001),"Speak to Bob",IF(D5<1001,5*E9,IF(D5<2001,D5*E10,D5*E11)))

 

[bluenose5709]

How about

=IF(OR(D5<=500,D5>=3001),"Speak to Bob",IF(D5<1001,5*E9,IF(D5<2001,D5*E10,D5*E11)))

Thank You, Much appreciated. this did the trick

 

[Fluff]

Glad we could help & thanks for the feedback.