Hi Giuseppe,

Welcome to our forum! Please try uploading your file again. After selecting the file, click the yellow 'start upload button' and then wait for the grey check mark beside the file size before clicking, 'submit reply'.

Your file created on Mac will open without issue in Windows Excel, so it should be fine. The most important thing is that you have an example of your desired result as we are Excel experts, not fulfilment experts, so Fba means nothing to us

Mynda

Hallo Mynda,
Thanks a lot. I’ll try to upload the file again.
Sorry if the question seemed inaccurate but it concerns excel and not amazon.
Fba means fulfillment by amazon. The values in the rate card tabs are the different shipping rates based on package size and weight. Now the question is:
Which function to use (nestedifs? sumifs?) to establish the right sending profiles related to size and volume in columns G and H?
Trying to be as clear as possible:
Colum G = if a product has a x size in a x market than it has a size profile y
Column H = if a product has a x volume in a x market than it has a volume profile z
Column J (the end result…but this simple if function is already there) = if the product is standard (has the size standard) then chose the volume profile z. If not the chose the size profile y.

I hope the file goes through.
IF the question is not clear I can try to rephrase it and add new elements again.

In any case really many thanks a lot for your time and patience.
Giuseppe

Hi Giuseppe,

Please see file attached - columns K - O. Hope that's what you're after.

Mynda

Hello Mynda,

Many thanks for your time. Unfortunately this will not work.

The shipping rate depends on the size of the product and in particular on 2 criteria:

The 1st operates like this:
IF the longest side is between 20 and 33 AND the weight is less than 80 grams then the product has profile 1
IF the longest side is between 33 and 35 AND the weight is less than 60 grams then the product has profile 2
IF the longest side is between 33 AND 35 and the weight is less than 210 grams then the product has profile 3
...and so on...(it is a series of IF - or a replacement boolean function - which scans the size of the products based on the long side and weight .... the Max function only considers the long side)

The 2nd criteria says.
For profiles from 7 to 44 choose the higher value between unit weight and volumetric weight (length x width x height/5)

My difficulty is in finding the suitable function that satisfies the first criterion

I don't want to take your precious time any further. I know the problem is that I can't explain the request properly. In any case, thank you for your help.

G.

Hi Giuseppe,

Thanks for clarifying. I think you can use the FILTER function to filter the Rate Card table for the possible rates based on the longest side, and then XLOOKUPthe weight and return the Profile.

I ran out of time and I don't think the formula is returning the correct results for every parcel, but I think it's on the right track. Hopefully, the file attached will point you in the right direction.

Mynda

Hello,

Not really sure I understood you correct Giuseppe, but I give it a try. Check if below formula give correct result in G2.

=XLOOKUP(1,
(Table1[Lange Seite]>=[@[longest-side]])*
(Table1[Mittel-Seite]>=[@[median-side]])*
(Table1[Kurze Seite]>=[@[shortest-side]])*
(Table1[Gewicht]>=[@Weight])*
(Table1[Fulfillment]="Volume"),
Table1[Profil],,1,1)

Br,
Anders

Hi Mynda, Hi Anders,

many thanks for your help and time. Yours solutions point me in the right way although the result for now does not match. I will open a new topic with a clearer excel file.

I realize i.e. that it would be easier if I add the column Parcel and the column Volume-Weight on both Tabs of the file.

It woul be than easier to write a formula that satisfy the 2 required criteria (that are actually 3 conditions: size, weight, Volweight):
1. choose the profile of the product (which size = column parcel) based on the value of its weight (column weight).
2. and choose the profile based on the greater of normal weight and volume weight

Exemple:

Product A:

19,4x11,6x5,8 = Small Parcel
Normal Weight = 1360,01 gr
Volume Weight = 261,05 gr

Right Profile = 10
because the normal Weight is bigger than Vol Weight an determine its profile

Many thanks again. This is really a special place.

Hello,

Your latest example corresponds to product 000.000.003 in FBA sheet, where the formula given in my previous post gives profile 10 as result. But let's say that the volume weight would be greater, as for product 000.000.025, what would be correct profile? The formula gives profile 23 as result, but as you see, that formula does not involve volume weight.

Br,
Anders

Hello Anders, 

Thanks for writing. Could you please share the file with your formula.? If I copy it ...it gives me an error. 

Anyway when the Volume weight is greater the than size weight it should give back the profile from Volume weight.

I will write back this evening.

BR

Giuseppe

Hi Anders,

The formula should be just like the following  (only the longest side and the weight define the size profile).

But unfortunately it still does't works on my file.

=XLOOKUP(1,
(Table1[Lange Seite]>=[@[longest-side]])*
(Table1[Gewicht]>=[@Weight])*
(Table1[Fulfillment]="Volume"),
Table1[Profil],,1,1)

Thanks anyway for your help

BR

Giuseppe