Part Number
13140253960
23140253960
33140253960
44140253960
Enter Part number in A2 to A5
Enter qty per carton C2 to C5
E2 to E5 formula e.g D2*C2
1 carton contains D2=20 units
if entered 2 cartons C2 then E2 2x20= 40 units
G2 is weight per carton = 10KG
if C2 if 2 cartons then H1 C2*G2= 20kgs
I2 formula to round off Ceiling(H2,1)
Also J2 is carton goes by pallet
J2 = 1 means 1 pallet
K2 displays 200 units per pallet
L2 displays pallet weight in KGS
M2 is L2*J2 e.g. 100 kgs x 1 pallet
which is Intl Prority tab C2
Q2- total weight of the carton
Displays based on Zone Country O2 and Weight I2 from Inlt Priority Tab
e.g. Dest USA-US-LAX / 20 kgs = 60
Displays based on Zone Country O2 and Weight I2 from Intl Economy
e.g. Dest USA-US-LAX / 20 kgs = 54
S2- total weight of the pallet
Displays based on Zone Country O2 and Weight M2 from Inlt Priority Tab
e.g. Dest USA-US-LAX / 100 kgs = 6700
T2- total weight of the pallet
Displays based on Zone Country O2 and Weight M2 from Intl Economy
e.g. Dest USA-US-LAX / 100 kgs = 5000
e.g A2/A3
Carton contains 20 each
If 13140253960 ship 40 untis then 2x20=40 units as lose cartons
If 23140253960 have 10 contains then 10x20=(K2)200 units since the threshold for this item max to 200 units then will be shipped as 1 x pallet not cartons or lose cartons
For A2 Since this lose cartons not pallet I like to only display Q2 and R2 not S2 or T2
For A3 since till will be going on as pallet then I like to display S2 and T2 not Q2 or R2
I need a formula that compared qty within E2 and K2
So if qty of the carton is less 200 then display Q2 and R2 if 200 equal to or more than display S2 abd T2
TAB Intl Priority
Look at A8 to 11
So for weight kgs has rate
A8 - if weight is between 21-44 kgs
e.g. 20 kgs x 9.52 = D8 190.40=20(C8)*9.52(B8)=190.40
A9 - if the wieght is between 45-70 kgs
e.g. 60 kgs x 9.09 = D9 545.40= 60(C9)*9.09(B9)=545.40
Hello,
You made a good effort trying to explain, but you did not attach a sample file.
https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
Br,
Anders
Hi Anders,
I really apologies for not uploading the sample file. I will try again.
Thank you,
Hello,
I need a formula that compared qty within E2 and K2
So if qty of the carton is less 200 then display Q2 and R2 if 200 equal to or more than display S2 abd T2
Add an IF statement in your exixting formulas in Q2 and R2 like so:
=IFERROR(IF(E2>=K2,”-”,INDEX( …
And for S2 and T2:
=IFERROR(IF(E2<=K2,”-”,INDEX( …
I hope I have understood your request correctly.
Br,
Anders
Hi Anders,
I really appreciate your great help and support to make it really look easy, you are a genius Sir.
I needed your guides with one more thing if only possible, otherwise, I will manually calculate the rate myself. It comes more complex what I am asking for.
Tab Main Rate Quote C2=2 / I2=22 kgs O2=France P=FRANCE-FR-TLS / Q2=369.60 / R2 330 from tab Intl Priority B8=22 kgs F2=FRANCE-FR-TLS Answer F8=369.60 and Tab Intl Economy B8=22 kgs F2=FRANCE-FR-TLS Answer F8=330
e.g. I have fixed-rate say from 1 to 20 kgs. Say weight is 22 kgs. Here I am having issues. If you look at tab Intl Priority and Intl Economy A8 to A 11 for both tabs.
Say if my e.g Tab Intl Priority A8 between limit between 21.0 – 44.0 B8= 22 (which I manually put 22) C8=16.80 rate C8*D8=369.60 / Tab Intl Economy A8 between limit between 21.0 – 44.0 B8= 22 (which I manually put 22) C8=15 rate C8*D8=330
If you look at tab Intl Priority and Intl Economy A8 to A 11 for both tabs.
Between 21.0 - 44.0 kgs has a different rate.
Between 45.0 - 70.0 kgs has a different rate.
Between 71.0 - 45,359.0 kgs has a different rate.
Between 45,360.0 - 99,999.0 kgs has a different rate.
I hope I am clear enough what I am asking for and only possible.
Thank you once again.
Hello Rakesh,
I have some questions so I know what is the best and/or easiest approach.
- What version of Excel do you have? If you are using Excel 365 then it is a lot easier to get what I think you want by using the XLOOKUP function.
- Is there any reason to why you have calculations in the two Intl Priority and Economy sheets? In my view they are lookup tables and should only contain data that you need to do calculations in main rate sheet.
- What is it you want to achieve with rows 8 to 11 that you can't get from rows 3 to 7?
Br,
Anders
Updated
I have added a sample file showing a simplified layout but still in line with what I think you are after. The sample file is not complete but it shows what I mean with having lookup tables as reference points. It might perhaps give some help or new ideas. I am also using XLOOKUP and ROUNDUP functions, where the first only works with Excel 365 and the latter with Excel 2007 and later.
