Multiple nested IF statements

[BLuse]

Hi,
I have a nested formula that I'd like to know if it's the best formula to use to get the results I need. It's working now, but I want to add some additional IF statements and I'm thinking I'm doing it wrong, there must be a better way.

So here's what I got. I have thousand's of SKU's listed on Amazon and I want to get the Amazon fulfillment fee for each. FBA fee's are tiered, so I would need to check each SKU for a range of conditions to see which tier it falls under and populate the fee based on that. Right now my formula only checks the size, it doesn't check what the weigh is. What would be the best way to get that.

*Note: G3 and G4 in my formula is where the fee value is stored.

Amazon fee guidlines

A	B	C	D	E
	Longest	Median	Shortest	Fee
Small <1 LB	15	12	.75	$2.41
Large <1 LB	18	14	8	$3.19
Large 1-2 LB	18	14	8	4.71
SM Oversize <2 LB	60	30	Longest+Girth<130"	8.13

<tbody>
</tbody>

Example Worksheet

A (Item)	B (L)	C (W)	D (H)	E (LB)	F (FEE)	Formula
Pen	6	.5	.5	.2	$2.41	=IF(MIN(B2:D2)<=0.75,IF(SMALL(B2:D2,2)<=12,IF(MAX(B2:D2)<=15,G3,IF(MIN(B2:D2)<=8,IF(SMALL(B2:D2,2)<=14,IF(MAX(B2:D2)<=18,G4,),),)),),IF(MIN(B2:D2)<=8,IF(SMALL(B2:D2,2)<=14,IF(MAX(B2:D2)<=18,G4))))
Camera	10	5	6	1.2	$4.71
Hanger	12	.5	8	.9	$3.19
60 gl Tote	30	12	18	1.6	$8.13

<tbody>
</tbody>

 

[Eric W]

It's generally best to build a table with your parameters, and let the formulas read the table. Much easier to read and modify. For example:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A (Item)	B (L)	C (W)	D (H)	E (LB)	F (FEE)			Longest	Median	Shortest	Fee
2	Pen	6	0.5	0.5	0.2	2.41		Small <1 LB	15	12	0.75	$2.41
3	Camera	10	5	6	1.2	3.19		Large <1 LB	18	14	8	$3.19
4	Hanger	12	0.5	8	0.9	2.41		Large 1-2 LB	18	14	8	4.71
5	60 gl Tote	30	12	18	1.6	0		SM Oversize <2 LB	60	30	Longest+Girth<130"	8.13

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula F2 {=MIN(IF((MIN(B2:D2)<=$K$2:$K$4)*(MEDIAN(B2:D2)<=$J$2:$J$4)*(MAX(B2:D2)<=$I$2:$I$4),$L$2:$L$4))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You'd probably want to put your table on another sheet. I couldn't make your sample data match my formula, so if I'm calculating it wrong, let me know.

As far as adding the weight to the formula, it shouldn't be too hard, but it depends on what the actual rules are.