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.
0
Connie (70)
1/19/2009 6:28:01 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
499 Views

Similar Articles

[PageSpeed] 37

On Jan 19, 8:28=A0am, Connie <Con...@discussions.microsoft.com> wrote:
> Hello,
> I require a formula that brings a result based on 2 different calculation=
s. =A0
> 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. =A0=
I will
> have a column for each range. =A0The second part of the calcuation is tha=
t the
> end result can't be less than $18 or more than $200.
>
> Here's a table that might help:
>
> Weight =A0 =A0 500 =A0 =A0 =A0 =A0 =A0 1000 =A0 =A0 =A0 =A0 =A02000
>
> 470 =A0 =A0 =A0 =A0 $19.10
> 750 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $35.00
> 1500 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0$50.00
> 220 =A0 =A0 =A0 =A0 $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 =3DLOOKUP("cell with
weight",A1:A5,B1:B5)

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

=3DIF(C1>200,200,IF(C1<18,18,C1))

regards,

Steven
0
1/19/2009 12:50:25 PM
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" <Connie@discussions.microsoft.com> wrote in message 
news:29269B2A-7398-48A4-A2D2-7329C6BAE550@microsoft.com...
> 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. 


0
bliengme5824 (3040)
1/19/2009 6:28:54 PM
Reply:

Similar Artilces:

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

time calculations #6
Hello! Does anyone know how you can calculate worked hours in a day on the basis of a start and end time? =(end_time-start_time)*24 -- HTH Bob Phillips "Aline" <A_Mangelschots@hotmail.com> wrote in message news:1127724380.330877.175210@o13g2000cwo.googlegroups.com... > Hello! > > Does anyone know how you can calculate worked hours in a day on the > basis of a start and end time? > Hi Aline With start time in column A, and end time in column B, enter in C1 =MOD((B1-A1),1) will give the time interval, even allowing for time period where the start is on...

MIN function with a variable range
I would like to use find the minimum of a selected range in a column. The range with be up to the current row position. The number of rows i the range is input in another cell. I am looking for a way to construc the range name that will work with the MIN function. I will copy th MIN function down to other rows, but the number of rows in the MI array will remain constant. Example: In cell F1 is "4", indicating that I want a range 4 ro long. I want to calculate the minimum of the indicated number of rows (i this case, 5) up to the row where the MIN function is. Say mi functio...

Calculating Tax on 100% Discount (POS V1.3.1006)
Hi everyone. Please check this: On the POS put the Discount (Shift-F3) to 100% for the whole transaction. Enter an item that requests the Price entry at the POS. Enter a price. Watch the Tax at the bottom of the screen. You get a receipt with a Total of 0,00 including a tax of what the regular tax would be if the item wouldn't have a 100% discount. Isn't that a bit weird? Chris try updating to 1.3.1011 and see if its fixed. "Christian Olsen" <ChristianOlsen@discussions.microsoft.com> wrote in message news:14D63EDA-C3B9-46F2-8720-75CA2725D429@microsoft.com... &g...

calculating risk
Hi I use a paper form at work that asks 3 multiple choice questions. The answer to each question is given a score, and the total score is then used to calculate whether risk is low, medium, high or very high. What I'd like to do is set this up in Excel 2002 so that the user simply clicks to select their choice for each question, and the score then results in the assessed risk level being printed on screen. Can anyone please either give me an outline of how to do this, or point me to where I can find a sample that I could modify. My email address as shown in newsgroups is fictitious. B...

Excel range truncates when Pasted as Picture to PPT & Word
Hi... I have been trying to copy an Excel Spreadsheet into PPT, but have had problems. In order to solve it, I created new .xls and .ppt files to create a test, but got the same problems... The following steps recreate the problem: 1) In a blank spreadsheet, I placed a single number in each cell, starting at A1 and going across to AS, until there are 1 through 45 across. Format them in some way... say Red text with an underline. 2) Set the width of all the columns to 2.00 (0.11 inches). 3) Select A1:AS 4) Copy (or Add to Scrapbook) -- The result is the same with both. 5) From Scrapb...

calculating Week numbers in a time table
I have a weekly timetable table for school terms with the following fields WeekID (automatic number), WkbeginDate (date), WkNo ( number 1 or 2). The WkNo can be either 1 or 2 . How can I get the Wkno field to automatically update with the correct week number for each new record ie if a record has week value 2 the next record will have a WkNo value of 1? I would prefer to do this using a query rather than a form. The timetable is not for the whole year and has 'holiday' breaks, sometimes restarting on a week 2, sometimes on a week 1. Hope this makes sense! Hilarys =?Utf-8?B?SGls...

calculating time (for payroll)
I'm trying to use Excel like a "time clock" to check in/out of work. I want to be able to write the time in a certain cell that I came into work, left for lunch, came back from lunch, and left for the day. I then want to add up the hours for the week and any hours up to 45 I need to multiply by a dollar amount. Also, any hours above 45 I need to calculate for overtime. I can add the ours to get a total for the week but am having a hard time with multiplying "Time" by a dollar ammount. (Nevermind that I have to find the amount of hours overtime) Attached is a very s...

Sum a range of Data that's not shaded gray
Hi, I trying to figure out how to SUMIF only if the data in a cell is not shaded gray. On my spreadsheet, once an item in a column is shaded gray, it is complete. If it is shaded any other color it is an accounts receivable. So I'm trying to figure a quick way to add up my accounts receivables. Any ideas would help...Thanks..Pete -- Pete Corrao www.PilotPeteSupplies.com (631)875-0550 See below page from Chip's site, http://www.cpearson.com/excel/colors.htm Regards, Shailesh Shah http://in.geocities.com/shahshaileshs/ (Excel Add-ins Page) If You Can't Excel with Tal...

in formula a range cell reference eg) $A1:$A20 that is static when filling
if i want a cell reference to not increment when filling i put a dollar sign in front of it i noticed this does not work when you specify a range of cells eg ) $A1:$A20 although it is not an error when i fill in a formula it still increments the cell reference in each row but if i specify a single cell it does not. does anyone know the syntax for this, it's hard to word so i havent been able to find anything on google. thanks for your help! cheers, /sh You mean like this: =SUM($A$1:$A$20) ? -- HTH, RD --------------------------------------------------------------------------- P...

between calculation?
Can anyone help me with a little problem? Im sure you can I have a row of cells with percentages in so looks something like this 189% 140% 130% 120% 116% etc etc. Any way what I want to acheive is in the colum next to this I woul like a formula that would say if the value in the percentage colum wa between 200%-175% then it would be 30p. Or if its between 174.99%-150 then it will be 25p. Working its way down to zero. Can this be done in Excel? As I dont know where to start I have trie a few formulas myself and got a response of complete mash : -- Message posted from http://www...

Export range of cells as high resolution image
If I try to cut & paste into a graphics program (I tried Photoshop and Paint) I get a low resolution image (96 dpi), suitable for viewing on screen, but not for printing. How can I get an hi-res image, in gif or (better) EPS format? Thanks in advance, Andrea Zadig wrote... > If I try to cut & paste into a graphics program (I tried Photoshop and > Paint) I get a low resolution image (96 dpi), suitable for viewing on > screen, but not for printing. > > How can I get an hi-res image, in gif or (better) EPS format? > > Thanks in advance, Hi Andrea, I have two m...

Increasing range for autofilter?
Is there anyway to increase the range of the autofilter without havin to use advanced filter? I have about 3,000 lines in my spreadsheet s far and its only showing about the first 1,000 of the category chosen If I must use advanced filter is there a link I can go to that wil show me. I am having trouble understanding the guide in Excel -- Message posted from http://www.ExcelForum.com Hi Have a look here: http://www.contextures.com/xlautofilter02.html#Limits -- Andy. "jkb724 >" <<jkb724.19ui1p@excelforum-nospam.com> wrote in message news:jkb724.19ui1p@excelforum-...

Calculating tax on rebates
Hello- I need to get a quick calulation to help me sovle a problem. I need to issue a $50.00 credit, but need to take a tax rate into account. I need to know the calculation if the $50.00 final credit amount is in A1, and the tax rate is in A2, what calculation would be in cell A3 that would show the pretax price? This is pretty straightforward algebra: f = p * (1 + r) So A3: = A1 / (1 + A2) In article <2dd290b7-5b71-470a-9af0-8cd45678f20a@j35g2000yqh.googlegroups.com>, Sabosis <scott.sabo@henryschein.com> wrote: > Hello- > > I need to get a quick calul...

Using a named range in a chart
Can a named range be used in a chart for one of the series? I have tried this, and it doesn't seem to work. For instance, i have a name _Date_rng defined (using Insert...Name...Define) as "data!$A$2:$A$60". In a chart (XY scatterplot), for one series, i define the X values as _Date_rng. Excel formats this as ={"_Date_rng"}. However, the chart is now blank, it draws with the axes, but no data is plotted. But, when i put the range directly in the chart "Source Data...Series" tab as "data!$A$2:$A$60", it plots the data correctly. So, is it po...

how to select a range that start in diferent row
Hi, please i need help. I have some worksheets with formulas and the last formula in the rows terminate with the word "END" i have a expression that find that word "END" and select that cell. Now i need to automaticaly select and delete all rows until row 3000 below that cell. This word are not in same rown in all worksheets this my dificult. Sometimes in range p200, p50, p989, etc i dont have same start range. I appreciate yor help, please help. I'm not quite sure I understand, but maybe this will help: Option Explicit Sub testme02() Dim FoundCell A...

HELP: subscript out of range econnect
Hi, I have SQL2005 /econnect 9.0.0 and BP30 but I can not install correctly econnect. When I try to check the list of companys that I have in GP90, the system send me warning error : "number:9" : subscript out of range. And I do not see any company, next the apply close. What can I do? Thanks, Leonardo Jadue C. ljadue@protab.cl ...

Assign a range to where the User Form places data
I have a user form with several text boxes and list boxes. I am having difficulties getting the data to be put in the right range. It goes to the next unused line and uses that line, however, there are couple lines at top of spreadsheet, so it uses those, instead of starting on line 4 as I would like. I have tried to select a range in which the lines are to be written, but not getting good results. Undoubtedly there is an easy answer. Thanks in advance for your attention. Hi As always post your macro for comments. Try and see if this can help you: TargetRow=3DRange(...

unknown range
I want to write a macro that selects a range that starts in A3 and ends in Jsomewhere. Is it possible to refer to an unknown cell in an known column? It might be nice to know what determines the "unknown" cell. =offset($a$3,0,0,counta($J:$J),10) could be a defined name range to find the last cell in J based on data in J -- Don Guillett SalesAid Software dguillett1@austin.rr.com "fanny" <f.klompsma@chello.nl> wrote in message news:76373$45b0fa73$3ea35143$7359@news.chello.nl... >I want to write a macro that selects a range that starts in A3 and ends in >J...

Calculated field
I am looking for the best way to create a report for attendance. Attendance is calculated by deducting the total number of absent employees (this is determined by user input) from the total number of employees on the roll divided by the authorized number of employees on the roll. The authorized number does not equal the total number of employees. The authorized number is input by management into a table. This same table has a manual input for the total number of employees on the roll. I would like to automate this second function with a DCount from the employee table but am having diffic...

calculation 02-06-08
My access book says that I should never include calculated fields in my tables, so I deleted them. Now I want to multiply a field by 2% and display the results. How can I do this? Use a query, form, or report. "Matt M." <matt.mawson@gmail.com> wrote in message news:d1a02b31-fefc-4b42-9b56-655aa8981ab7@s12g2000prg.googlegroups.com... > My access book says that I should never include calculated fields in > my tables, so I deleted them. > > Now I want to multiply a field by 2% and display the results. How can > I do this? On Wed, 6 Feb 2008 09:05:41 -0800 ...

Using a value from a previous Record to Perform a Calculation?
I have a Append query that looks up a particular date range I specify and gives me a count, for received and loaded dates that meet that range. From those 2 numbers the query also subtracts Received - Loaded=Backlog. See below ID Received Loaded Backlog New Backlog 1 0 0 0 0 2 5 3 2 ?????? What I need is for the query to lookup the New Backlog value from the previous record an...

Calculated Field 10-29-07
Im fairly new to access, and have a quick question. I have 1 table with a bunch of fields. of those fields, i have Open and Delivered (as in a # of open emailed, and # of delivered emails). Id like to divide the two fields to find a percentage, and then store that number in a field called Open Rate, which also resides in the same table. how can i SAVE this number to the table? On Mon, 29 Oct 2007 10:25:08 -0700, rogerscmg@gmail.com wrote: > Im fairly new to access, and have a quick question. I have 1 table > with a bunch of fields. of those fields, i have Open and Delivered (as &g...

using a rolling date range to sum
I want to use an update query to sum values of the previous twelve months. I tried using BETWEEN "#" & [date from] & "# AND #" & [date to] & "#" in the criteria to limit records summed. I get a error message telling me I did not include the and. I really would like to find a source that has the explaination of each symbol and the parameters of the built in functions. My dates are short date and time and always the first of the month. I have several years of data that needs the rolling sum calculated so using a fixed date woul...

Multiple Consolidation Ranges
Hi Debra The answer to your question "What happened when you tried it?" is a message appears "ODBC Excel Driver login failed" "Unrecognised database format" Maybe the information posted by Arvi Laanemets will help you: http://groups.google.ca/groups?&threadm=usUvRuTJFHA.3484%40TK2MSFTNGP12.phx.gbl nc wrote: > Hi Debra > > > The answer to your question "What happened when you tried it?" is a message > appears "ODBC Excel Driver login failed" > "Unrecognised database format" > -- Debra Dalgleish ...