If, Then formula help

[cgendron]

Hello all,
i need some help with a formula. In column B i'll enter a value, depending on that value, column D will give me a rate, either "Free, B, C, D or E"

So what i want the formula to say is:

If B11 is less than or equal to B9 then "Free". If B11 is between D9 and E9 then "B Rate". If B11 is between G11 and H11 then "C Rate. If B11 is between J9 and K9 then "D rate". If B11 is between M9 and N9 then "E Rate".

i'd also like something to populate like "No Discount" if none of these statements is true.

Thanks.

 

[philwojo]

Are the lower "lettered" cells always going to contain a lower value, meaning will D9 always be less than E9 and G11 always less than H11?

 

[dreid1011]

Perhaps this:

edit: well I forgot about the forum shenanigans with < >'s.

Let's try this again:

=IF(B11<=B9,"Free",IF(AND(B11>D9,B11<E9),"B Rate",IF(AND(B11>G11,B11<H11),"C Rate",IF(AND(B11>J9,B11<K9),"D Rate",IF(AND(B11>M9,B11<N9),"E Rate","No Discount")))))

 

[cgendron]

Correct, thanks

 

[cgendron]

Are the lower "lettered" cells always going to contain a lower value, meaning will D9 always be less than E9 and G11 always less than H11?

Sorry, meant to reply with your quote. Your assumption is correct, thanks

 

[philwojo]

Not really too much different than DREIDs, but here is my version. Mine assumes it can be equal too D9 or E9 not just between them, so not sure which version works for you.

=IF(B11<b9,"free",if(and(b11>=D9,B11<=E9),"B Rate",IF(AND(B11>=G11,B11<=H11),"C Rate",IF(AND(B11>=J9,B11<=K9),"D Rate",IF(AND(B11>=M9,B11<=N9),"E Rate","No Discount")))))

</b9,"free",if(and(b11>

 

[cgendron]

Perhaps this:

edit: well I forgot about the forum shenanigans with < >'s.

Let's try this again:

=IF(B11<=B9,"Free",IF(AND(B11>D9,B11<E9),"B Rate",IF(AND(B11>G11,B11<H11),"C Rate",IF(AND(B11>J9,B11<K9),"D Rate",IF(AND(B11>M9,B11<N9),"E Rate","No Discount")))))

That worked perfectly! Thanks for your help!

 

[dreid1011]

Happy to help, thanks for the feedback.