XL2010 Quotation Margin Auto Calculation Formula

[AliUAE]

Appreciate it in getting assistance with the Formula in XL2010 for <o></o>

Rate (Sell D11) calculation for different Venders, Products and Sizes.<o></o>

The margins (profit percentage over the cost price) are different<o></o>

for each Vender. The Venders and Products list are over 50 numbers. A B C D E F H I J K L M N O P Q R S No Products Size Rate Quantity Line Total Cost Price 1 Size XXXL XXL XL L ML M S SS 2 Margins Vender Product 3 1.20 Vender A T-Shirt 110 100 95 90 85 80 75 70 4 1.30 Vender B Trousers 200 190 180 170 160 150 140 130 5 1.15 Vender C Jacket 250 225 200 175 150 125 100 75 * Jacket XL 220 2 440 1.40 Vender D Hat 50 45 40 35 30 25 20 15 * Shirt ML 60.5 3 181.5 1.12 Vender E Shirt 75 70 65 60 55 50 45 40 * Thew above lines 16&17 are examples Line Total 621.5 1.10 Vender F Socks 20 18 16 14 12 10 8 6 to how the formula calculation result Discounts 1.20 Vender G Underwear 30 29 28 27 26 25 24 23 should be. Tax 1.50 Price List Total 621.5 <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[Comfy]

Something like this:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Products	Size	Rate	Quantity	Line Total					Cost Price
2									Size	XXXL	XXL	XL	L	ML	M	S	SS
3						Margins	Vender	Product
4						1.2	Vender A	T-Shirt		110	100	95	90	85	80	75	70
5						1.3	Vender B	Trousers		200	190	180	170	160	150	140	130
6						1.15	Vender C	Jacket		250	225	200	175	150	125	100	75
7	Jacket	XL	200	2	440	1.4	Vender D	Hat		50	45	40	35	30	25	20	15
8	Shirt	ML	55	3	181.5	1.12	Vender E	Shirt		75	70	65	60	55	50	45	40
9	* Thew above lines 16&17 are examples			Line Total	621.5	1.1	Vender F	Socks		20	18	16	14	12	10	8	6
10	to how the formula calculation result			Discounts		1.2	Vender G	Underwear		30	29	28	27	26	25	24	23
11	should be.			Tax		1.5	Price List
12				Total

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C7 =INDEX($H$2:$Q$8,MATCH(A7,$H$2:$H$8,0),MATCH(B7,$H$2:$Q$2,0)) C8 =INDEX($H$2:$Q$8,MATCH(A8,$H$2:$H$8,0),MATCH(B8,$H$2:$Q$2,0)) <tbody> </tbody>

<tbody>
</tbody>

In your example you listed ML shirts as 60.5 but your cost price list says 55 and XL Jackets as 220 rather than 200.
Please clarify.

 

[AliUAE]

Something like this:
Thanks for your promote response and suggested formulas.Yes the examples are correct:
Vender F margin is 10% over the cost price (costx1.1)
Jacket XL cost price is 200 x 1.1 = 220 (sell price)
Shirt ML cost price is 55 x 1.1 = 60.5 (sell price)



Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Products	Size	Rate	Quantity	Line Total					Cost Price
2									Size	XXXL	XXL	XL	L	ML	M	S	SS
3						Margins	Vender	Product
4						1.2	Vender A	T-Shirt		110	100	95	90	85	80	75	70
5						1.3	Vender B	Trousers		200	190	180	170	160	150	140	130
6						1.15	Vender C	Jacket		250	225	200	175	150	125	100	75
7	Jacket	XL	200	2	440	1.4	Vender D	Hat		50	45	40	35	30	25	20	15
8	Shirt	ML	55	3	181.5	1.12	Vender E	Shirt		75	70	65	60	55	50	45	40
9	* Thew above lines 16&17 are examples			Line Total	621.5	1.1	Vender F	Socks		20	18	16	14	12	10	8	6
10	to how the formula calculation result			Discounts		1.2	Vender G	Underwear		30	29	28	27	26	25	24	23
11	should be.			Tax		1.5	Price List
12				Total

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C7 =INDEX($H$2:$Q$8,MATCH(A7,$H$2:$H$8,0),MATCH(B7,$H$2:$Q$2,0)) C8 =INDEX($H$2:$Q$8,MATCH(A8,$H$2:$H$8,0),MATCH(B8,$H$2:$Q$2,0)) <tbody> </tbody>

<tbody>
</tbody>

In your example you listed ML shirts as 60.5 but your cost price list says 55 and XL Jackets as 220 rather than 200.
Please clarify.

Thanks for your promote response and suggested formulas.Yes the examples are correct:
Vender F margin is 10% over the cost price (costx1.1)
Jacket XL cost price is 200 x 1.1 = 220 (sell price)
Shirt ML cost price is 55 x 1.1 = 60.5 (sell price)

 

[Comfy]

Ah, ok. I understand. It wasn't clear due to the layout of the tables.

I have moved the tables a little so that it is clearer but you can obv change the layout back.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Vender	Products	Size	Rate	Quantity	Line Total
2	F	Jacket	XL	220	2	440		Vender	Margins		Cost Price	Size	XXXL	XXL	XL	L	ML	M	S	SS
3	F	Shirt	ML	60.5	3	181.5		A	1.2		Product
4	C	Hat	S	23	4	92		B	1.3		T-Shirt		110	100	95	90	85	80	75	70
5	G	Socks	XL	19.2	1	19.2		C	1.15		Trousers		200	190	180	170	160	150	140	130
6								D	1.4		Jacket		250	225	200	175	150	125	100	75
7								E	1.12		Hat		50	45	40	35	30	25	20	15
8								F	1.1		Shirt		75	70	65	60	55	50	45	40
9					Line Total	732.7		G	1.2		Socks		20	18	16	14	12	10	8	6
10					Discounts						Underwear		30	29	28	27	26	25	24	23
11					Tax
12					Total	732.7
Sheet1

Cell Formulas
Range	Formula
D2	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B2,$K$2:$K$10,0),MATCH(C2,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B2,$K$2:$K$10,0),MATCH(C2,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2))
D3	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B3,$K$2:$K$10,0),MATCH(C3,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A3,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B3,$K$2:$K$10,0),MATCH(C3,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A3,$H$2:$H$9,0),2))
D4	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B4,$K$2:$K$10,0),MATCH(C4,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A4,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B4,$K$2:$K$10,0),MATCH(C4,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A4,$H$2:$H$9,0),2))
D5	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B5,$K$2:$K$10,0),MATCH(C5,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A5,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B5,$K$2:$K$10,0),MATCH(C5,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A5,$H$2:$H$9,0),2))
D6	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B6,$K$2:$K$10,0),MATCH(C6,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A6,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B6,$K$2:$K$10,0),MATCH(C6,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A6,$H$2:$H$9,0),2))
D7	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B7,$K$2:$K$10,0),MATCH(C7,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A7,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B7,$K$2:$K$10,0),MATCH(C7,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A7,$H$2:$H$9,0),2))
D8	=IF(ISNA(INDEX($K$2:$T$10,MATCH(B8,$K$2:$K$10,0),MATCH(C8,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A8,$H$2:$H$9,0),2)),"",INDEX($K$2:$T$10,MATCH(B8,$K$2:$K$10,0),MATCH(C8,$K$2:$T$2,0))*INDEX($H$2:$I$9,MATCH(A8,$H$2:$H$9,0),2))
F2	=IFERROR(D2*E2,"")
F3	=IFERROR(D3*E3,"")
F4	=IFERROR(D4*E4,"")
F5	=IFERROR(D5*E5,"")
F6	=IFERROR(D6*E6,"")
F7	=IFERROR(D7*E7,"")
F8	=IFERROR(D8*E8,"")
F9	=SUM(F2:F8)
F12	=F9-F10+F11

 

[AliUAE]

Dear Comfy
I appreciate the time you have taken to rearrange the table, which is much neater now.
Also like to thank you for setting out the formulas, specially for the rate cell.
Couldn't figure out that it's going to be this long, I really appreciate the time you have spend on it.
Already tried it, it's working great. I am happy man now. Cheeeerssss Comfy.