Formula for Avg Margin Per SKU per 4 Qty's

[Tom Edwards]

I have a list of 20,000 sales with 1500 different SKU's.
The list shows the SKU, The quantity sold,the total sale
and the Margin generated for that line of data. I would
like to have a breakdown into 4 graduating quantities of
the average margin (based on the overall total sales that
each of the 4 quantities generated per SKU). The quantity
grouping varies depending on the type of item.

Thank you,

Tom Edwards
_________________________________________________________

Sample:


SKU QTY SOLS TOTAL SALE MARGIN

BLT0431Z 10 $29 34%

BLT0431Z 15 $47 33%

BLT0431Z 57 $148 20%

BLT0431Z 75 $186 24%

BLT0431Z 123 $358 34%

BLT0431Z 124 $366 30%

BLT0431Z 434 $1,037 12%

BLT0431Z 543 $1,314 13%

BLT0431Z 846 $2,073 20%

BLT0431Z 1521 $3,726 14%

RA218RA166 1 $103 28%

RA218RA166 3 $331 33%

RA218RA166 4 $441 33%

RA218RA166 5 $460 21%

RA218RA166 8 $704 15%

RA218RA166 9 $792 16%

RA218RA166 10 $840 12%

RA218RA166 10 $980 24%

RA218RA166 25 $2,200 15%
_____________________________________________________


1+ 100+ 300+ 500+

BLT0431Z avgM%? avgM%? avgM%? avgM%?


1+ 3+ 6+ 10+

RA218RA166 avgM%? avgM%? avgM%? avgM%?

 

[sebastienm]

Hi,
Assuming:
- data is from row 3 to 100
- SKU is in column A
- TOTAl SAlE is in col C
- Margin in col D
- SKUitem is entered in A105

ttl $ sale for this sku. In C105, enter:
=SUMIF(A$3:A$100,$A105,C$3:C$100)
ttl $ margin for this sku. In D105, enter:
=SUMPRODUCT((A$3:A$100=$A105)*(C$3:C$100)*(D$3$100))
Ttl % Margin, say in %105
=D105/C105

Regards,
Seb

 

[NHarkawat]

To make it easier we will use a helper column So just insert a column after
Quantity (so that Column C is our helper column)
Insert the formula on Col C and copy across 20000 rows of data to basically
dstinguish 4 categories of Quantity (<10,<100, <500 & > 500)
=IF(B2<=10,"A",IF(AND(B2>10,B2<100),"B",IF(AND(B2>100,B2<500),"C","D")))
Using Filter Advanced filter - Unique records separate the SKU's in a
separate area
On the top row on 4 columns above these Uniques SKUs insert type A,B, C, D
tyoe the folwing formula
=SUMPRODUCT(--($A$2:$A$11=$G2),--($C$2:$C$11=H$1),($E$2:$E$11))/COUNTIF($C$2
:$C$11,H$1) and copy in across the region
May have change the cell references accordingly