Calculating ranges
 

Hello,
I require a formula that brings a result based on 2 different calculations.
First I need to calculate a dollar amount for weights between the ranges of 0
to 500 lbs, 501 to 1000 lbs, 1001 to 2000 lbs and so on up the scale. I will
have a column for each range. The second part of the calcuation is that the
end result can't be less than $18 or more than $200.

Here's a table that might help:

Weight 500 1000 2000

470 $19.10
750 $35.00
1500 $50.00
220 $18.00

Any help will be much appreciated.
Thanks.

Calculating ranges
 

On Jan 19, 8:28*am, Connie wrote:
Hello,
I require a formula that brings a result based on 2 different calculations. *
First I need to calculate a dollar amount for weights between the ranges of 0
to 500 lbs, 501 to 1000 lbs, 1001 to 2000 lbs and so on up the scale. *I will
have a column for each range. *The second part of the calcuation is that the
end result can't be less than $18 or more than $200.

Here's a table that might help:

Weight * * 500 * * * * * 1000 * * * * *2000

470 * * * * $19.10
750 * * * * * * * * * * * * $35.00
1500 * * * * * * * * * * * * * * * * * * * *$50.00
220 * * * * $18.00

Any help will be much appreciated.
Thanks.

Connie,

1st formula to return the dollar amount per weight:
Set up a table with minimum weight range in 1st column (e.g.
0,501,1001 in range A1:A5)and the respective dollar price in the 2nd
column(e.g. in range B1:B5).

It is important to ensure that the table is arranged in ascending
order.

Insert the following formula in cell C1 =LOOKUP("cell with
weight",A1:A5,B1:B5)

2nd formula to ensure that result is 18 and<200

=IF(C1200,200,IF(C1<18,18,C1))

regards,

Steven

Calculating ranges
 

Not sure I understand the max values but:
Let A1:A10 have a list of weights
In B1 enter =LOOKUP(A1,{220,470,750,1500},{18,19,35,50})
Copy this down to B10
Anywhere you wish find the total with =SUM(B1:B10)
If this is where the max applies =MAX(200,SUM(B1:B10))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Connie" wrote in message
...
Hello,
I require a formula that brings a result based on 2 different
calculations.
First I need to calculate a dollar amount for weights between the ranges
of 0
to 500 lbs, 501 to 1000 lbs, 1001 to 2000 lbs and so on up the scale. I
will
have a column for each range. The second part of the calcuation is that
the
end result can't be less than $18 or more than $200.

Here's a table that might help:

Weight 500 1000 2000

470 $19.10
750 $35.00
1500 $50.00
220 $18.00

Any help will be much appreciated.
Thanks.