If and then formula

[GAB]

Hi,

I need help writing a formula that will automatically allow the right price point to be selected. I don't even know if this is possible. I have two "prices each" that need to be selected based on quantity. So if the quantity is less than 1001 then price A would be selected or if the quantity is greater than 1000 then price be would be selected. Is this something that can be done as a formula?

Thanks,

Gary

 

[CazB]

In a word: yes

You need to use an IF statement
=if(logical test,value if true, value if false)

the logical test must be a formula to which the answer can only be 'true' or 'false' - in this case: is the quantity less than 1001?

The value if true is what it should do if it is TRUE - in this case, use priceA

The value if false is what it should do if it is FALSE - in this case, use priceB

=if(quantity<1001,pricea,priceb)

(obviously, insert your cell references in the approriate places!)

 

[Brianwarnock]

As presented

If(quantity<1001,priceA,priceB)

Is the basic function except quantity will be probably represented by a cell address such A1 as will the prices presumably.

If should be in help

Brian

I see cazb typed faster than I did

 

[MSAccessRookie]

Hi,

I need help writing a formula that will automatically allow the right price point to be selected. I don't even know if this is possible. I have two "prices each" that need to be selected based on quantity. So if the quantity is less than 1001 then price A would be selected or if the quantity is greater than 1000 then price be would be selected. Is this something that can be done as a formula?

Thanks,

Gary

The Standard configuration for an IF() Statement should get you close to what you want.

IF([Logical Test], [Value if True], [Value if False])

Your [Logical Test] would be [Quantity <= 1000]
Your [Value if True] would be Price A
Your [Value if False] would be Price B

Of course, you will need to fill in the proper references for Price A and Price B

-- Rookie

Looks like I was too slow on my post once again.

 

[GAB]

You guys are awesome responding this quickly. Thank you for that. From what you gave me this is what I did: =IF(E4<1001,0.22*E4,0.05*E4) and it works!!!!!!! Thank you again

Gary

 

[Brianwarnock]

Looks like I was too slow on my post once again.

You may have got bronze for speed but certainly gold for presentation

Brian