Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Calculating shipping costs

Hello everyone. I'm a newbie here and have searched the New Users threads with no luck.

I have a small sales business and have created a workbook to figure pricing. The problem I'm having is calculating shipping costs. We use UPS and they charge by the pound. I can total the weight of all items on an order but I can't figure out how to make the worksheet find the shipping costs based on the weight totals.

For example, the freight weights are:
Weight, lbs Price
Under 3 5.95
3 - 6.9 6.85
7 - 13.9 7.85
14 - 23.9 8.90
24 - 39.9 0.35 per lb
40 - 59.9 0.31 per lb
and so on down to
Over 1000 0.24 per lb.

I've tried some of the VLOOKUP formulas in this forum without luck. I just don't know what I'm doing.

Can someone help?

Thank you very much,

Lynn


Post your answer or comment

comments powered by Disqus
I have a list of shipping costs for instance, if a product costs between 0.00
and 10.00 shipping is $3.00, if it's between 10.01 and 20.00 shipping is
$4.00.

I've got the first part down, but I don't know how to do the rest of the
formula with the if/then and OR statements.

This is all I can get to work so far and I have to put a price range from 0
to $600.00 in so that whatever the subtotal comes out to be the shipping is
automatically calculated.

Any helf would be greatly appreciated.

Hi everybody,

I have been trying for hours to get my head around this one so I came to the conclusion that I needed help. I am trying to make a formula that takes a products price and calculates the Western union fee/shipping costs:

If a products price is:
under $50 = $12
Under $100 = $14
Under $200 = $21
etc etc

I have got this far:
=IF(OR(A15<=50), 12, "other')

I know it is a little more complicated than that so I am scratching my head at this one. If someone could point me in the right direction (link to the post with an answer) or they could give me a basic formula, I would be really grateful.

Thanks

Leigh

I want to calculate the shipping costs for orders. I have a cell which
totals the weight. I have the shipping weights with corresponding rates. I
want the "shipping cost" cell to look at the weight cell and calculate the
shipping rates for a range of weights. e.g. weight is 336 lbs and there is a
rate for a range of 325-350lbs. This needs to be able to provide the rate
for a wide range of possible weights from 15 to 2,500lbs. Thanks for your
help

Hi - I could use some advice on how to accomplish dividing up the total shipping cost between the quantity of items then divide per line item. This of course would only apply when an invoice contains multiple items. Column G is where I need the output.

The sample below contains the cells required for calculation. You will notice A3 (Invoice 12-001) is only one line item, thus no calculation for column G is needed. However, any time there is an invoice with multiple records, (i.e. A4-A6, A7-A8.) a shipping split will need calculated. So, I am thinking:

1.) step one is establishing whether or not there are multiple line items (column A), If not (example A3) enter the value of F4
2.) Sum the qty per invoice (example invoice 12-345 would be 37)
3.) divide column D by the sum totaled from step 2 (example D4/37 = 0.54)
4.) Multiply 0.54 by total shipping amount (example $12.50 = $6.76)

SAMPLE.jpg

Any suggestions?

I want to calculate the shipping costs for orders. I have a cell which
totals the weight. I have the shipping weights with corresponding rates. I
want the "shipping cost" cell to look at the weight cell and calculate the
shipping rates for a range of weights. e.g. weight is 336 lbs and there is a
rate for a range of 325-350lbs. This needs to be able to provide the rate
for a wide range of possible weights from 15 to 2,500lbs. Thanks for your
help

Hi there,

this is my first post, cause I really need your help out there.

I am trying to put together a sheet to calculate item cost after shipping and taxes. To be honest I may have complicated the whole thing but what I have is two

order volumes that need to be changed to calculate the most cost effective solution.
For example: Item 1 (a1) is $40 and item 2 (a2) is $10, if I was to order 100 (a3) of (a1) and 50 (a4) of (a2), then multiply them in (a5) and (a6) to give me total

order values without taxes etc. I then have (a7) which is the shipping (a8) the import tax and (a9) the VAT.
What I am stuck on is how to work out how much a1 and a2 costs me per item, if I was to order 100 of each then I think it would be simpler by calculating

the total cost and divide by 100 then again by 25% which is the cost difference between $40 and $10. So although I may have gone around the houses on this

one, I want to be able to change cells a3 and a4 and then see how much it is costing per item after shipping and taxes.
Thanks for looking.

I'm interrested in mr.excel products (VBA examples and mr. Excel on Excel)

But, what are the shipping cost and time? Knowing that I'm from Montreal, Canada...

Or maybe there is a bookstore here that already have it, but I never seen it before.

I am working on calculating the cost drivers for my organisation.

We have 19 “Departments”, 11 “Head of the Departments” and around 1000+ “Employees”.

Due to Job rotation, Supervisory, Internal Expertise etc staff are often pulled / allocated to other department out of their parent department.

Form the staff attendance software we could get the monthly data of each staff worked in which department.

I have attached a sample database and my requirements.

I would appreciate if someone could help me getting the formula in the highlighted range of the attached worksheet.

Regards

Swastik

I need to calculate a cost based on a time element given, which also has a minimum charge. It's for calculating long distance charges for customers. I receive a .csv file with their usage and I have to calculate the end user charges based on the length of the call.

I receive the call usage in seconds. My rate is 3.5¢ per minute. Customers are billed for the first 30 seconds regardless of the length of the call, then in 6 second increments after. So if it's a 2 second call, they get billed for a half minute (0.0175¢)

I originally calculated the formula as this: =(((D2/60)-0.5)*0.035)+0.0175 where D2= the number of seconds, divided by 60 to give the total minutes. It then subtracts the first half minute and multiplies the difference by the per minute charge of 3.5¢. The first half minute is then added on at the end. The reason I did it this way is because if the call is only 10 seconds long, the customer needs to get billed for 30 seconds. So if D2/60-0.5 equals less than zero, the minimum 0.0175¢ charge needs to apply. The problem with my formula is that if D2 is less than 60, it becomes a negative and doesn't calculate properly. I think what I need is an IF statement that if the D2/60-0.5 equals less than zero, the answer is zero. Or something that would set the minimum charge at 0.0175¢ if it's anything lower than that. The formula works except in instances where the call is under 60 seconds. And that's a lot of calls. I hope this wasn't too confusing an explanation! Any help would be appreciated.

Hi there
I've been trying to create a spreadsheet for a holiday shop and I've gotten nowhere in 2 hours.

I need these columns and will have almost 200 items

item, qty, total cost, cost each, 40% disc cost each, price, count, sold, profit

I know the Qty and the total cost and will input the price we charge and then count after the sale is over. I would like the cost before and after the 40% disc and then figure out a formula for the amount sold and the profit.
the problem was I was trying to calculate the cost for each widget using QUOTIENT but it was giving me $3.00. My calculator gives me $3.35.

I am going to go back and look at the autosum for the sold column but I can't seem to be able to figure out cost per piece much less the disc or profit

I would appreciate any help with this.
thanks
g

I have attached a sample of the problem I am trying to solve. The table is labeled from item A to I. Below that is the cost of each item. I will have people selecting the items as shown.
I have manually calculated the total cost but don't know where to start with calculating the cost.

I want to have a calculated total cost.

Any help would be greatly appreciated.

Thank you in advance

Here's a question for an assignment for my computer info systems class.

I have a table posted below in cells A4:E13

Credit National National National National
Rating Area Area Area Area
Score 111 112 113 114
300 3.00% 3.00% 3.00% 3.00%
325 3.25% 3.25% 3.25% 3.25%
350 3.50% 3.50% 3.50% 3.50%
475 4.25% 4.50% 4.75% 5.00%
550 4.50% 4.75% 5.00% 5.75%
650 5.50% 5.25% 6.00% 6.50%
750 6.50% 5.75% 7.00% 7.25%

Shipping Rate Per Pound Shipped is $4.00 (4 is placed in cell E16)

B21:G25 for this partial example of the table...
Credit Shipping Total Total
Customer Rating Destination Weight to Discount Shipping
Number Score Area Ship (lbs.) Amount Cost Invoiced
100234 487 111 2,987 ? ?
100543 300 114 15,487 ? ?
etc. etc. etc...

I need to calculate the Discount Amount based upon a customer's credit
rating score and from there calculate the total shipping cost for that
customer. I must do this TWO ways using the VLOOKUP function. I must do
these both where I only have to type the formula once and can just copy it
down for each row of the table that calculates shipping costs. I must
provide two versions of the VLOOKUP formulas: 1) VLOOKUP using a nested IF
to definte the col_ind_num, and 2) VLOOKUP using a calculation to define the
col_ind_num.

I cannot figure out how to properly use the function -without- using nested
IF functions. Any suggestions?

P.S.
The current formula I have is
=VLOOKUP(C24,$A$5:$E$13,IF(D24=$B$6,2,IF(D24=$C$6, 3,IF(D24=$D$6,4,IF(D24=$E$6,5)))),TRUE)*E24*$E$16

Hello,
Trying to calculate shipping cost from table on seperate sheet.If shipping is over $200 than its free otherwise need to lookup value on seperate sheet. On the seperate sheet have table shipping with 2 columns.one with subtotal amount i.e (0-54.99) and then second column cost at 5.95. the following was my formula which doesn't work.

=if(h38>=200,0,Vlookup(h38,shipping,2,0)).

Thanks-jeromephoto

Here's a question for an assignment for my computer info systems class.

I have a table posted below in cells A4:E13

Credit National National National National
Rating Area Area Area Area
Score 111 112 113 114
300 3.00% 3.00% 3.00% 3.00%
325 3.25% 3.25% 3.25% 3.25%
350 3.50% 3.50% 3.50% 3.50%
475 4.25% 4.50% 4.75% 5.00%
550 4.50% 4.75% 5.00% 5.75%
650 5.50% 5.25% 6.00% 6.50%
750 6.50% 5.75% 7.00% 7.25%

Shipping Rate Per Pound Shipped is $4.00 (4 is placed in cell E16)

B21:G25 for this partial example of the table...
Credit Shipping Total Total
Customer Rating Destination Weight to Discount Shipping
Number Score Area Ship (lbs.) Amount Cost Invoiced
100234 487 111 2,987 ? ?
100543 300 114 15,487 ? ?
etc. etc. etc...

I need to calculate the Discount Amount based upon a customer's credit
rating score and from there calculate the total shipping cost for that
customer. I must do this TWO ways using the VLOOKUP function. I must do
these both where I only have to type the formula once and can just copy it
down for each row of the table that calculates shipping costs. I must
provide two versions of the VLOOKUP formulas: 1) VLOOKUP using a nested IF
to definte the col_ind_num, and 2) VLOOKUP using a calculation to define the
col_ind_num.

I cannot figure out how to properly use the function -without- using nested
IF functions. Any suggestions?

P.S.
The current formula I have is
=VLOOKUP(C24,$A$5:$E$13,IF(D24=$B$6,2,IF(D24=$C$6,3,IF(D24=$D$6,4,IF(D24=$E$6,5)))),TRUE)*E24*$E$16

I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula:
VLOOKUP(D32,Shipping!A2:B52,2).

My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.

I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these.

I’ve attached a sample spreadsheet, Shipping_Charge.xls.

In advance, I thank you for your help.

I’m wondering if anyone here knows of a UPS shipping cost calculator in Excel? I have a fully functional calculator in an old Microsoft Works spreadsheet, but it took weeks to make and likely would take just as long to convert to Excel.

I’ve been searching the web for something similar, no luck, but I’ll continue searching.

I'm trying to figure out a formula to calculate future cost estimates for capital projects using original prices and inflation data.

Referring to the main sheet as "MAIN", I have the inflation data on a separate sheet "INFL" in two columns. Column A has the year, Column B has the percentage of inflation for that year.

"MAIN" has columns for "Original Price" (I'll call it r), "Original Purchase Year" (p), "Projected Purchase Year"(j), and "Projected Price". The formula I need belongs in the "Projected Price" column.

Basically, what I'm looking for is:
=SUMPRODUCT(p:j*r)+r

But I can't figure out how to get Excel to pull a range of data (a set of years defined by two separate columns on sheet "MAIN") from sheet "INFL". I can get a specific and correct projected cost for an item that was purchased in 2010 and will be purchased again in 2011 using, for example:

=SUMPRODUCT(INFL!B2:INFL!B3*r)+r

But this does not automate the year range input the way I would like. I have to manually type in the cell which corresponds to each year. I've tried using SUMIF and VLOOKUP but no luck. Maybe I'm formatting it incorrectly. Suggestions?

Hi,

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment.

The objective is to end up with a labour cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!).

My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong.

I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39 ???????????

I am sure I have missed something straightforward,

Help.....

Looking for a formula that can calculate the handling cost of 1,140lbs for goods that can either originate from UST or PGD:
If origin PGD, $0.10 p/lb with a minimum handling cost of $25, or
If origin UST, $0.10 p/lb with a minimum cost of $45.

In C1,I can calculate the handling cost, but I am not sure how to make the calculation if the DST is "UST":

A1 B1 C1
DST WEIGHT HLDG-DST
PGD 1140 IF(B1*0.1>=25,(B1*0.1),"25")

I'm working on a problem that calculates data using a binomial distribution. The data derived from the binomial distribution is then used to calculate a cost.

I would like to minimize cost by changing the number of "reservations". Can excel solver do this or is it too complicated? I have attached the file with what I'm working on. (Changing E1 to minimize E2 while Cells A9:A102 are calculating a binomial distribution)

Here is the deal, I've got product for sale in every state by multiple suppliers. I want to calculate the average cost per state by supplier. here is the data:

Item City State Supplier 1 Supplier 2 Supplier 3
A Chicago IL 15 13 12
B Moline IL 9 19 8
C Rockford IL 9 18 9
D Cleveland OH 14 12 10
E Mentor OH 15 13 11

So again, I want two things from this. I want the average cost per supplier/state (So for Supplier 1 in IL I'd get 11 & in OH I'd get 14.5) How do I work that out assuming that the title row is A1 and Item A is listed in A2 and so on?

The other thign i need is a way to run a formula that will tell me Your average cost for all of IL is X, OH is Y and so on for every state I have. Catch is that there are different numbers of suppliers in each state, and there are different numbers of products in each state. Any ideas?

Hello,
I'd like a spreadsheet to calculate total cost of a purchase order, but need an elegant way to account for different per-piece cost for different order sizes.

As an example, if the customer orders 1-9 pieces, the cost per piece is $0.60. 10-49 pieces costs $0.45 per piece. 50-99 costs $0.40, etc.

I need a formula to calculate total cost using the appropriate per-piece cost given the size of the order.

Is there an elegant formula (vlookup, perhaps?) that can help me out?
Many thanks for your help
B

Does anyone have a spreadsheet to calculate average cost basis for a mutual fund? I can create a sheet that calculates the average cost but have not figured out the best way to account for sales of the fund.

Thanks

Mr. Excel,
I've been asked by a friend to help with the automatic calculation of shipping times. His supervisor wants to know the length of time it takes to from date, hour & minute a package was shipped to the date, hour & minute it was delivered EXCLUDING weekends.

How can this be done? I've tried a couple of things, but cannot get the weekends out of it.

Sam Gill CPS


No luck finding an answer? You could always try Google.