And/ Or issue....how to formulate a formula...racking my brains :(

[helloanupam]

Calculating FBA Charges (Column Z)[/U]
To determine "Item Size": Use Column R

The FBA charges are dependent on
1.Item Size i.e Small or Medium Or Over Size
2.If Small size then first 0.5kgs are charged 30 and then incremental 0.5kgs upto 1 kg is also charged at 30 (Small Size weight limit : < = 1kg)
Thereafter it comes under "Standard" size, also in which case first 0.5kgs are charged 30 and then incremental 0.5kgs(upto 2 kg) is also charged at 30. After 2 kgs and upto 12 kgs charged at 15 per500gms (Standard Size Weight Limit: < =12 kgs)
3.Oversize: Since Item size is based on dimensions AND weight either one parameter is crossed then the next Item sizetakes effect. So although Standard size accepts upto 12 kgs , if the dimensions exceed that of Standard size then it would automatically be categorized as Oversize. So oversize item based on dimesion but weight upto (< 5kgs) is charged flat 80 (for first 5kgs). Afterwhich, each additional kg would be charged 9/kg.

Can any one please help to formulate a formula. Myself , unable to do so.
Excel file attached.

Regards

[amit.wilson]

hello helloAnupam Anupam (there's an echo in here) :-D

try this:

Please Login or Register  to view this content.

Quick explanation:
1. If the item is oversize, first 5 kgs cost 80.
2. Additional kgs above 5kg cost 9 per kg: ROUNDUP(MAX(Q4-5),0)*9
3. If item is not oversize, count number of 0.5 kg units upto 2 kgs and pay 30 for each: ROUNDUP(MIN(Q4,2)/0.5,0)*30
4. And above 2kg [Q4-2], count number of 0.5 kg units and pay 15 for each: ROUNDUP(MAX(Q4-2,0)/0.5,0)*15

I've used Roundup assuming that 1.25kgs will cost the same as 1.5kg. And 6.1 kgs will cost the same as 7 kgs.

What do you think?

Cheers