Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating within Date Ranges | Excel Discussion (Misc queries) | |||
Calculating Date Ranges | Excel Worksheet Functions | |||
Calculating the number of values between specified ranges | Excel Discussion (Misc queries) | |||
Calculating rate based on age ranges | Excel Worksheet Functions | |||
Calculating with named ranges | Excel Discussion (Misc queries) |