varible calculation

I am trying to create a calculation for haulage/trucking charges

1-300 kilos  £ 30 
300-400  kilos £ 35
400-500 kilos £ 40

What formula could I use to accomplish this??

     



-- 
kinsey
0
kinsey (18)
9/29/2007 8:26:03 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
467 Views

Similar Articles

[PageSpeed] 47

assuming that you mean:

1-299
300-399
400-

then try:

=LOOKUP(A1,{0,300,400},{30,35,40})

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"kinsey" <kinsey@discussions.microsoft.com> wrote in message 
news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com...
>I am trying to create a calculation for haulage/trucking charges
>
> 1-300 kilos  � 30
> 300-400  kilos � 35
> 400-500 kilos � 40
>
> What formula could I use to accomplish this??
>
>
>
>
>
> -- 
> kinsey
> 


0
sandymann2 (1054)
9/29/2007 8:36:21 PM
if the weight is in cel A1 you could try 
=IF(A1>300,IF(AND(A1>300,A1<400),35,40),30)
you will need to change it slightly as you have 2 values for 300 & 400 
kilo's.  You might also want some validation to prevent the cell having a 
value over 500 kilo's
-- 
John 
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"kinsey" wrote:

> I am trying to create a calculation for haulage/trucking charges
> 
> 1-300 kilos  £ 30 
> 300-400  kilos £ 35
> 400-500 kilos £ 40
> 
> What formula could I use to accomplish this??
> 
>      
> 
> 
> 
> -- 
> kinsey
0
John1063 (943)
9/29/2007 8:46:22 PM
This formula presumes that you have a cell named weight and that weight is 
not 0, and that any weight over 400 kilos is � 40.  You can make it more 
elaborate. For a few more weights, you could do a table lookup.

=IF(weight<=300,30,IF(weight<=400,35,40))

"kinsey" <kinsey@discussions.microsoft.com> wrote in message 
news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com...
>I am trying to create a calculation for haulage/trucking charges
>
> 1-300 kilos  � 30
> 300-400  kilos � 35
> 400-500 kilos � 40
>
> What formula could I use to accomplish this??
>
>
>
>
>
> -- 
> kinsey 


0
Wondering1 (40)
9/29/2007 8:57:16 PM
First I think you have a couple of typos.  You can't have two rates for 300 or
400

Maybe you mean  

1-300
301-400
401-500

or do you mean?

1-299
300-399
400-500

Will there ever be fractions like  299.96 kg?

Will there ever be more than 500 kg?

If so, would the max be �40

=LOOKUP(A1,{1,301,401,501},{30,35,40,40}) 

Try the above............adjust ranges as needed.


Gord Dibben  MS Excel MVP 


On Sat, 29 Sep 2007 13:26:03 -0700, kinsey <kinsey@discussions.microsoft.com>
wrote:

>I am trying to create a calculation for haulage/trucking charges
>
>1-300 kilos  � 30 
>300-400  kilos � 35
>400-500 kilos � 40
>
>What formula could I use to accomplish this??
>
>     

0
Gord
9/29/2007 8:57:53 PM
Sorry, didn't pay enough attention. The formula will not work. Your criteria 
need clarification.

1-300 kilos is � 30, 300 - 400 kilos is � 35. You can't have that. 300 kilos 
is both � 30 and � 35 and 400 kilos is both � 35 and � 40.



> "kinsey" <kinsey@discussions.microsoft.com> wrote in message 
> news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com...
>>I am trying to create a calculation for haulage/trucking charges
>>
>> 1-300 kilos  � 30
>> 300-400  kilos � 35
>> 400-500 kilos � 40
>>
>> What formula could I use to accomplish this??
>>
>>
>>
>>
>>
>> -- 
>> kinsey
>
> 


0
Wondering1 (40)
9/29/2007 9:03:03 PM
many thanks 
-- 
kinsey


"Sandy Mann" wrote:

> assuming that you mean:
> 
> 1-299
> 300-399
> 400-
> 
> then try:
> 
> =LOOKUP(A1,{0,300,400},{30,35,40})
> 
> -- 
> HTH
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "kinsey" <kinsey@discussions.microsoft.com> wrote in message 
> news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com...
> >I am trying to create a calculation for haulage/trucking charges
> >
> > 1-300 kilos  £ 30
> > 300-400  kilos £ 35
> > 400-500 kilos £ 40
> >
> > What formula could I use to accomplish this??
> >
> >
> >
> >
> >
> > -- 
> > kinsey
> > 
> 
> 
> 
0
kinsey (18)
9/30/2007 7:21:00 AM
works a treat! many thanks
-- 
kinsey


"Gord Dibben" wrote:

> First I think you have a couple of typos.  You can't have two rates for 300 or
> 400
> 
> Maybe you mean  
> 
> 1-300
> 301-400
> 401-500
> 
> or do you mean?
> 
> 1-299
> 300-399
> 400-500
> 
> Will there ever be fractions like  299.96 kg?
> 
> Will there ever be more than 500 kg?
> 
> If so, would the max be £40
> 
> =LOOKUP(A1,{1,301,401,501},{30,35,40,40}) 
> 
> Try the above............adjust ranges as needed.
> 
> 
> Gord Dibben  MS Excel MVP 
> 
> 
> On Sat, 29 Sep 2007 13:26:03 -0700, kinsey <kinsey@discussions.microsoft.com>
> wrote:
> 
> >I am trying to create a calculation for haulage/trucking charges
> >
> >1-300 kilos  £ 30 
> >300-400  kilos £ 35
> >400-500 kilos £ 40
> >
> >What formula could I use to accomplish this??
> >
> >     
> 
> 
0
kinsey (18)
9/30/2007 7:50:00 AM
Reply:

Similar Artilces:

calculations on vlookup during a macro
I have a macro the does a vlookup from a different worksheet in the same workbook. It is a large file, 50,000 rows. After the vlookup is complete, I insert columns next to the results of the vlookup and the insertion causes the vlookup to re-calculate and it slows the macro down. All I am doing is inserting a few columns. Is there a way to make that part of the macro speed up, but not continually re-calculating. I know that I can paste values and then delete the vlookup column with the formula. Was wondering if there was another way. Thanks. Kenlyn Maybe insert your columns before yo...

Automatic Calculate Does Not Work !
This happened to a spreadsheet that i used very often once. A formula which has link to cell in in other sheet suddenly does not auto-calculate after i changed the input value in other sheet. I got to select the cell with formula and press F2 to force it update the calculated value. I was so surprised bcos this never happened before! I then exit Exel and open the same spreadsheet again, that problem never happened any more. Im wondering why and when this happen, and have i made any "mistake" on the value i calculated before by using that spreadsheet. Is this so-called "tr...

Calculating Average over many worksheets
I have 19 worksheets, all set up with the same column designations but with a varying number of rows in use. I need to calculate an average for all the numerical values in Column C on all the worksheets. Currently the most rows in use on a worksheet is 1987 and the least is 67. As a quick solution , I copied and pasted them all into one column on a new worksheet and averaged at the bottom but there must be a far better way. Thanks Pepper If you don't have any other numbers not to be included in column C you can use =AVERAGE(First:Last!C:C) and if you create new sheets than you s...

Excel 2007 calculation bug fix
A fix has been released for the Excel 2007 calculation bug: http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx -- Biff Microsoft Excel MVP ...

Storing data from a calculation in a table
I've poked around and found that an expression I make in a form is unbound to the table where I would like it stored. Other posts have stated that method is a waste of disk space and it's better to do in a query or leave it in the form. Here's my problem: I have 200 subjects that complete a long survey. Each survey gets totaled up. I have it so I can see what the total is in the form for each subject. I need to pull these totals (not the individual numbers that went into each total) for each subject either into a query, a table, or SOMEWHERE I can get all the total sco...

Cell doesn't appear in calculations
I used an IF function to generate the cells in one column. E.g., IF(g3<=25,"25,IF(g3<25,g3)). Now I want to use the results of that column in summing something else and the sum function basically doesn't 'see' the cell at all. The results are as if there were no numbers there at all. What gives? I assume you want this in the formula =IF(G3>=25,25,IF(G3<25,G3)) anything larger than 25 is 25 or it could be =IF(G3<=25,25,IF(G3>25,G3)) anything smaller than 25 is 25 in any case do not use "25" it is treated as text in the summing exc...

Copy Calculated Field in Report Writer
Is there a way to copy a calculated field in report writer from one report to another? Thanks, Jocelyn You can copy a calculated field from one report to another by creating a package file using customization maintenance and then editing the file using notepad. You need to change the table names if necessary and be very careful. Which field from which report did you want to copy and to which report? I've done the address block and can post it to my blog (I should do that today anyway). You can copy the entire report layout using the same method. Like for instance the SOP Blan...

Age calculation
Is there a way in Word2000 to calculate the age of a person based upon a REF'd date-of-birth entry in a online form? -- Fantomegl See http://www.gmayor.com/insert_a_date_other_than_today.htm and in particular the linkewd download from that page - http://www.gmayor.com/downloads.htm#Third_party -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<>...

Re: Calculate A Date Excluding Weekends And Holidays
I have a date and a series of tasks that need to be completed by a certain number of days from the intial date. The time to complete the tasks can not include weekends or holidays. Example: Initial date is 2/15/2006 I have 10 business days to complete a task All Help will be appreciated. Thank you. -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17623 View this thread: http://www.excelforum.com/showthread.php?threadid=510028 A good starting place for info...

Using value from Dataset 2 in calculation in Dataset 1
Hello- Bumping in to an exception that I cant seem to get around. Report has two datasets. I need to use a value that dataset2 returns. (Dataset 2 is just a stored proc that returns a single value.) Dataset1 has a calculated field that needs to reduce the value in a field by the value that is returned from dataset2. I think the way to do this is to reference the textbox.value that holds the value from dataset2: calculated field = field_from_dataset1.value - sum(ReportItems! credit.value) where ReportItems!credit.value is the textbox that has the value returned from DS2. ...

A calculation in a form
The form I use to enter purchasing data calculates a total cost per order, but it doesn't put this info into the data table. How do I get it to do this? (All the other data from the form goes to the corresponding field in the table) Unless there is some business reason, (like say for audit purposes) you never store calcualtions. They just take up space and memory. Instead, maybe try putting the calculations in a query and refernce the query from your form so that every time you run the form it gives you fresh calculations. -- Milton Purdy ACCESS State of Arkansas ...

Excel 2002 Pivot Table calculated field percentage.
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C59754.7AF34EE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the following pivot table format: Please note that the numbers are calculated fields for net debit and = credits. (Column) Year 1998 % 1999 % 2000 % Total (Row) Accounts xxxx $456 ?? $552 ?? $600 ?? $1608 xxxx xxxx I would like to calculate the percentage variance for each account = (xxxx) per year based upon the previous year. Having problems i...

Need opinion on Project test questions and calculations
The following two questions, with answer key and explanations, were on a recent quiz over MS Project and assigning resources. I have queried the author(s) of this quiz, who are supposedly are qualified project consultants, but have yet to get any explanation as to the validity of their statements/correct answers. Here are the questions with the answer keys: 1. If you have 400% allocated for Builder, what ways can this be represented in Microsoft Project using a weekly duration? For this question, you will choose more than one option for your answer. 4 resources at 100% for ...

Adding payroll stubs payroll calculator
I am trying to add more payroll stubs on my payroll calculator. I have 5 employees and it only gives me stubs for four of them. I need to be able to add more with out having to set it up myself, as I don't know how. if by stubs you mean tabs or sheet in a excel workbook all you need to do is right lick on a "stub" and selet move or copy,you have a choice where you want to move or copy it to.To copy it you must click in the create a copy box You will get an exact copy of the sheet you copied,dont forget to give it a new name by right clicking on it and rename otherwise you w...

How do I create a calculated field on the Total in a pivot table?
How can I create a calculation from the total of a count of a field on a pivot table? Example: I need to calculate the number of employees for each process level times a specified amount - 89 x $4.59, 3 x $4.59. Count of Name - Full Segment Process Level Total 302 302 89 305 3 302 Total 92 When I attempt to create a formula =counta('Name - Full')*4.59 I get an error. ...

How can I calculate trend growth rates in Excel?
I need help calculating the trend growth rate of GDP of a country over a 20 year period. Thanks! Can you provide the equations needed to do this calculation? "david34" <david34@discussions.microsoft.com> wrote in message news:AB0616EE-EF3C-42E9-A98D-C1774B68866A@microsoft.com... >I need help calculating the trend growth rate of GDP of a country over a 20 > year period. > > Thanks! I'm not sure which equations you are referring to. I have GDP numbers in millions of dollars for a 20 year period and I'm trying to calculate the trend growth rate for t...

Unbound field calculation save in table
I have a score form that I created in Access 2003 the only part of the score sheet I am interested in saving to the table is the total score. Because the score sheet is all unbound fields and the total score is a control source calculation how can I save the total score to a field in the table called score. sharrg2 wrote: >I have a score form that I created in Access 2003 the only part of the score >sheet I am interested in saving to the table is the total score. Because the >score sheet is all unbound fields and the total score is a control source >calculation...

How to calculate the present value of a security, $5000 @ 7%, 20y.
I'm trying to use excell to calculate the present value of a security that will pay $5000.00 in 20 years at 7% interest. PV=FVn/(1+I)N There is a PV function in Excel. Have a look in the Excel built-in Help for more info on how to use the function. Regards, Tom "catslgc" wrote: > I'm trying to use excell to calculate the present value of a security that > will pay $5000.00 in 20 years at 7% interest. PV=FVn/(1+I)N ...

Oppurtunities not calculating
How can I get my oppurtunities to calculate volume discounts? The quotes do this just fine, but the oppurtunities do not... Any help would be appreciated. The issue is that opportunities do not use discount lists to support volume discounts. The altnerative (not a good one) is to set the opportunity to not calculate; set it as user provided and have the user enter the amount of the opportunity. Linda >-----Original Message----- >How can I get my oppurtunities to calculate volume discounts? > >The quotes do this just fine, but the oppurtunities do not... > >Any h...

This calculation is just wrong / computer can't count!
I have been debugging something for ages now. I have a method that does some complex maths, but right at the beginning it works out a proportion and a few ratios and the maths is simply wrong. In my code I (obviously) use variables and the values vary each time the method is called, but there seems to a problem with the maths. I have narrowed the problem down to the following. Can someone else please try this simply calculation and see what their computer gets. Code Line 1: double effortChangeProportion = (55.0 - 30.0) / 30.0; This first line does 55-30 and divides the result by 30. I...

Software about financial calculators
I am interested in an aplication or a software that can be used like a financial calculators. If it is for free, better. Hi, If you have specific needs, maybe(?) I can create one for you. visit my sites: http://www.geocities.com/excelmarksway http://au.geocities.com/windsofmark regards mark excelmarksway@yahoo.com.au >-----Original Message----- >I am interested in an aplication or a software that can >be used like a financial calculators. If it is for free, >better. >. > ...

Formula to calculate an age from a date of birth
I'm looking for the formula to calculate the age from a date of birth. One way: =DATEDIF(A1,TODAY(),"y") where A1 contains the DOB. See http://cpearson.com/excel/datedif.htm for documentation on DATEDIF(). In article <FBF2C1DE-CFEF-442D-BD61-2C0CFCEFC682@microsoft.com>, Lisa C <anonymous@discussions.microsoft.com> wrote: > I'm looking for the formula to calculate the age from a date of birth. for more than just years, just expand. from xlfdic01.xls, if date is in D34... ="Age is "&DATEDIF(D34,TODAY(),"y")&" Years &...

How To Refresh Chart Data Without Calculating The Worksheet
I am trying to refresh a chart in an Excel 2000 worksheet without recalculating the whole worksheet. Tried using the following syntax to no avail (this is not a pivot chart): ActiveSheet.ChartObjects("Chart 70").Select ActiveChart.Refresh Thanks, Carl Carl, Please try one of these: Sub RefreshChart1() ActiveSheet.ChartObjects("Chart 70").Activate ActiveChart.Refresh End Sub or Sub RefreshChart2() Dim Cht As Chart Set Cht = ActiveChart Cht.Refresh End Sub ---- Regards, John Mansfield http://www.pdbook.com "Carl Bowman" wrote: > ...

Worksheet().Range.Calculate Problem
I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. There's no need to select the worksheet in o...

Calculated fields in Smartlist
Is it possible to display calculated fields in SmartList? Rather than push calculated fields back into the database, I think the only alternative is to pump the data out to Excel and do calculations there. Can someone confirm this? Thanks, Rich SmartList won't read any calculated fields. "Richard Rook" wrote: > Is it possible to display calculated fields in SmartList? Rather than push > calculated fields back into the database, I think the only alternative is to > pump the data out to Excel and do calculations there. Can someone confirm > this? Thanks, ...