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.