anyone good with excel?

[tropikalz]

I'm hoping to get some help if someone can create a formula for me in excel I want to have a cell where I can enter the weight of an item and based on the weight I have a range of tables that will give the end result the value

By entering the weight if it's between 0 - 10 LBS I've sort of posted my chart below it cost 8.76 if the package is less then 10 lbs however when the item goes between 11 to 50 lbs an additional dollar is added to ever pound?

Any one willing to give it a shot and help me out i'd really apprecaite it ... maybe via msn if I'm not explaining to well..


To: VANCOUVER VICTORIA WESTERN BC EASTERN BC NORTHERN BC OKANAGON CALGARY EDMONTON

BASE RATE
0 - 10 LBS 6.7 7.73 8.76 8.76 13.91 7.73 8.76 8.76
ADD PER LB
11 -- 50 0.79 0.111 0.156 0.245 0.279 0.168 0.134 0.156
51 -- 100 0.79 0.111 0.156 0.245 0.279 0.168 0.134 0.156
101 -- 300 0.79 0.111 0.156 0.245 0.279 0.168 0.134 0.156

[Jmac]

I thought we were adding an extra dollar per pound ?

[Stevie P]

So you want different rates depending on which city they choose? And the additional $ on every lbs over 10lbs or the total weight? Your "ADD PER LB" rates are all the same too.

[tiger_handheld]

seems easy..... but i dont understand ur numbers.... mayb if u stick it in a excel sheet.. that will help...

its using IF Statements..

[wix]

I did it, i have a 4 chain if statement formula =)

[wix]

=IF(C13<=10,C13*B3,IF(C13<=50,(10*B3)+((C13-10)*C3),IF(C13<=100,(10+B3)+(C13-10)*C3+(C13-50)*D3,IF(C13<=300,(10*B3)+(C13-10)*C3+(C13-50)*D3+(C13-100)*E3,"Limit Reached"))))

[tropikalz]

Thanks Wix and everyone else Wix has some idea of what I was heading toward probably because I didn't explain it well enough for everyone to understand....


The city really doesn't matter we'll in the long wrong it does but while creating teh formula all I need is IF 0 - 10 then the cost is 6.79

Then if the range goes between 11 and 50 you go

6.79 + .79 cents to each pound

So if it was a 12 pound 6.79 + 2 * .79 = total


I'm going to try what Wix has it seems like it might work because thats what I was trying to put into excel at the time when I couldn't get it to work ....

[tropikalz]

Wix the formula works however it doesn't do what I need it to do it's multipling another sell ....

I need some where between the IF function and the LOOKUP if i get it i'll let you guys know

6.79 (0 -10)

IF 6.79 goes over 10 I need it to add .79 per pound up to 50 and then if it reaches above 50 add .89 per pound if the weight goes between 50 and 100 and not the .79 per pound you would get when it's between 11 and 50...

I know it's hard to understand when it's written then viewing it but I'll try to post a screenshot tonight of what i'm trying to do to help soemone help me..

Thanks\

[misteranswer]

So...this is like income taxes.



=if(c13<=10,6.79,if(c13<=50,(c13-10)*0.79+6.79,if(c13<=100,(c13-50)*0.79+40*0.69+6.79,if(c13<=300,(c13-100)*whatever is per lbs between 300-100+50*0.79+40*0.69+6.79))))

Something like that

[tcneo]

so in writing..
eg weight is 132lb
- 132lb - 10lb = $6.79
- (122lb remainder) the next 40lb = 40 * .79 = $31.6 (11~50lb)
- (82lb remainder) the next 50lb = 50 * .89 = $44.5 (51~100lb)
- (32lb remainder) the next 32lb = 32 * .99 = $31.68 (101~150lb)
so in total it costs $6.79 + $31.6 + $44.5 + $31.68 = $114.57
is that how you want it calculated? or
- 132lb - 10lb = $6.79
- (122lb remainder) since 122 is between 101 ~ 150 => 122 * .99 = $120.78
so in total it costs $6.79 + $120.78 = $127.57

which way do you want to calculate it?

[wix]

I have the file, pm me your email if you want.

It is 100% correct, I am a professional accountant

And misteranswer is correct, it is exactly like income taxes, but his formula just adds the rates together and will give you the wrong end result.

The question is asking to multiple the first 10 lbs by a certain rate, then 11-50 by another rate, then 51-100, by another rate. So it's a bit more complicated.

Here is my spreadsheet.

[img=http://img17.imageshack.us/img17/6854/72331289.jpg]

[tcneo]

why don't u separate the tiers to make it easy to check, afterwards you can always hide or combine the result.
here's mine, but you just have to adjust the pricing table to reflect the correct multiplier
http://rapidshare.de/files/46844915/excel.xls.html

[wix]

I like how you separated the tiers :0 good addition, but your end price is severely incorrect.

For the first option, if you put 11 lbs, u get a price of $14.xx. Checks it out

I see what you're trying to do, you can do the rates thing like what i did, and then just =sumproduct() for the price, very pro

Yours would be better than mine once u add that function in

[tcneo]

that's from the table above, if you put the difference per lb then the calculation is correct.
All you need is to edit the table above.
eg
use this instead of the one that you have on your picture
Location 0-10 LBS 11-50 LBS 51-100 LBS 101-300LBS
Vancouver 6.70 0.79 0.79 0.79

with this table, I get the end price of $7.49 for 11lbs Vancouver which I believe is correct?

[tropikalz]

Hey I really appreciate the help on this one I'm very surprised how you guys hit the nail on the head! TCNEO & Wix! Really appreciate this you saved me tons of time going through hours of paper work...

TCNEO: I reviewed your file and at first I was wondering what was going (probably because I’m slow and not the best with excel) but after reviewing it and adjusting some of the numbers to my needs I did the calculations and it's bang on!!!!

Wix? I'd love to get a copy of the file you generated in your screenshots if possible to my email: chainreactionz@hotmail.com

This is going to be used to do a comparison at my work between several couriers wanting to do business with us unfortunately I had the task of going through the documents cost / zoning sheets which is several hours of work comparing postal codes entering costs for each zone and with your formula I’m going to expand the spreadsheet to several more cities and several different costs and variations but with spread sheet I know have the starting formula to do the rest…. Thank you again and it’s going to be really hard to copy and expand what you did but if I can’t I’ll just copy and paste and keep adding different areas

Thanks

[tropikalz]

TCNEO - I hope it's not to much to ask can you please check the file you made and extend the formula all the way to 20,000 lbs?

I've changed the file to add the new formula as you created it but I couldn't get it to go furthur because it's to complicated for me atleast

[tropikalz]

guess the link would helP:
http://rapidshare.com/files/225048035/excel.xls.html

[tropikalz]

Hey TCNEO


actually after reviewing it again and going over your formula I think I got it ...

http://rapidshare.com/files/225054873/excel.xls.html

[tcneo]

your total formula is missing something there's 10 tiers in the table, but your calculation only shows 9 tiers. I'll see what I can do later..

[tropikalz]

haha I knew I was doing something wrong... I'll try playing with it again but yah if you have time again i'd appreciate it but if not thanks for the help up until now.

[tcneo]

there you go, I modified & changed some of the formulas to make it easier to copy
http://rapidshare.com/files/225287148/excel.xls.html
also, I didn't put any check function to make sure that the yellow column stays 10 or above. You'll have to do it manually. Just don't want to make the formula even more complicated.

[tropikalz]

Thanks TCNEO works great.