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

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

- How to calculate shipping costs based on subtotal
- Designing a formula to calculate western Union fees and shipping costs
- Shipping costs using if then
- Dividing shipping costs per line item
- Shipping costs using if then
- Calculate item costs
- Shipping cost
- Cost Driver calculation - Looking for formula/macro to calculate manpower cost alloca
- Calculating a Cost with a minimum charge
- 'Problem calculating retail costs and discounts'
- Calculating the cost of multiple items
- VLOOKUP and multiple columns
- help with ifvlookup function.
- VLOOKUP and multiple columns
- Shipping Charge Calculator
- Shipping Calculator?
- Reference variables on other sheet in cost calculation formula
- Time and cost calculations
- Calculate Handling Costs Based On Cell Value
- Minimize Cost By Calculating Best Binomial Distribution
- Calculate Avgerage Cost By State
- Total Cost Calculation
- Calculate cost basis
- Calculating Shipping time EXCLUDING weekends

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.

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

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

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?

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

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.

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.

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 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.

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 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

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

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

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

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’ve been searching the web for something similar, no luck, but I’ll continue searching.

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?

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.....

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 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)

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?

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

Thanks

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