combining if then & range functions

[repke]

I'm trying to create a simple price sheet for freight. I don't know ho
to insert multiple qualifiers into a single formula. I'm also unsur
of what formula to use (is there a multipy if formula?).

I have a price list that gives a flat rate for certain ranges and
percentage to be multiplied against the amount of weight.

if between 1 and 50 lbs = $25
if between 51 and 300 lbs * 12%
etc.

I readily admit I have no idea what I'm doing but was dumb enough t
volunteer for this project so I'm hear begging for help. Gracias !

Chri

 

[FxM]

Something like :
(With your weight in A1)
=if(A1<1;"Hum";if(A1<51;25;if(A1<300;A1*12%;"too high")))

Limit : 7 times if(..) -> 8 results possible

@+
FxM



repke < a écrit :

 

[Trevor Shuttleworth]

Chris

this would work for up to 7 "options" (IF conditions)

=IF(AND(A5>0,A5<=50),25,IF(AND(A5>50,A5<=300),A5*12%,"next option"))

For example, to extend the theory a little ...

=IF(AND(A5>0,A5<=50),25,IF(AND(A5>50,A5<=300),A5*12%,IF(AND(A5>300,A5<=550),
A5*10%,"next option")))

If there's more, you probably need to set up a table and use VLOOKUP. You
could check the Help or search the Google Archives for example of VLOOKUP.

Regards

Trevor

 

[BrianB]

Not really enough information to give a definitive answer.

1. You do not say how many different rates there are. If there are man
it would be better to have them in a lookup table and use the VLOOKUP(
function in the formula.

2. In you examples you are mixing value and weight. It does not matte
whether the percentage is applied to weight or value, the answer wil
be the same.

3. Presumably you have a lookup table price list which shows Produc
and Price. You can use the =VLOOKUP() function to find the price of
product.

I4. The calculation basis is to multiply weight times price time
percent (which will be 100 if no discount is applied).

5. The IF() function works this way.
=IF(some condition, value if condition true, value if condition false)

The best way to get a formula is to build it in stages.
Have a column each for Product/Price/Weight and Calculation.
First get your Price lookup for the Product to work in one column. The
get your Percentage lookup depending on weight to work.
Then do your calculation.

I suggest a new post for any further problems showing any formulas yo
are having problems with. Mamy of us do not read messages that alread
have answers

 

[repke]

Thanks Brian! I appreciate it, I will give it a shot and if I stil
have problems I will repost trying to fill in the gaps you pointed out