I have a spreadsheet where column G is a list of UPC codes for many products and column P is a list of costs (at various locations and at various times) for the items. I want to make a column that shows the average cost for that row's item (determined by matching the UPC column "G") by averaging out all of the costs (Pack Cost column "P") associated with that UPC.
The purpose of this is to vett the cost reported by the site (the data in each row) vs the average cost reported by all sites for the item.
I sure hope someone can understand what I wrote.
The purpose of this is to vett the cost reported by the site (the data in each row) vs the average cost reported by all sites for the item.
I sure hope someone can understand what I wrote.
| data (15).xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 1 | Site Name | Province | TM | transDate | deptDescription | subDeptDescription | upc | Item Description (original) | TY GM% | GM% Low Range | GM% High Range | TY QTY | TY Sales | TY Cost | TY GM$ | Pack Cost | Pack Sell | ||
| 2 | 1705-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.81% | 4.00% | 12.00% | 5 | $95.97 | 27.05 | $68.92 | $5.41 | $19.19 | ||
| 3 | 1705-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.72% | 4.00% | 12.00% | 7 | $134.00 | 37.89 | $96.11 | $5.41 | $19.14 | ||
| 4 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.59% | 4.00% | 12.00% | 13 | $247.63 | 70.35 | $177.28 | $5.41 | $19.05 | ||
| 5 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.54% | 4.00% | 12.00% | 4 | $76.04 | 21.64 | $54.40 | $5.41 | $19.01 | ||
| 6 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.54% | 4.00% | 12.00% | 8 | $152.08 | 43.28 | $108.80 | $5.41 | $19.01 | ||
| 7 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.54% | 4.00% | 12.00% | 4 | $76.04 | 21.64 | $54.40 | $5.41 | $19.01 | ||
| 8 | 1705-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.54% | 4.00% | 12.00% | 16 | $304.24 | 86.6 | $217.64 | $5.41 | $19.01 | ||
| 9 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.53% | 4.00% | 12.00% | 8 | $152.08 | 43.29 | $108.79 | $5.41 | $19.01 | ||
| 10 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.53% | 4.00% | 12.00% | 18 | $342.18 | 97.42 | $244.76 | $5.41 | $19.01 | ||
| 11 | 1705-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.52% | 4.00% | 12.00% | 4 | $76.06 | 21.66 | $54.40 | $5.41 | $19.01 | ||
| 12 | 1705-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.52% | 4.00% | 12.00% | 4 | $76.06 | 21.66 | $54.40 | $5.41 | $19.01 | ||
| 13 | 3881-Swan River | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.27% | 4.00% | 12.00% | 2 | $37.66 | 10.82 | $26.84 | $5.41 | $18.83 | ||
| 14 | 3881-Swan River | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.27% | 4.00% | 12.00% | 2 | $37.66 | 10.82 | $26.84 | $5.41 | $18.83 | ||
| 15 | 3881-Swan River | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 71.26% | 4.00% | 12.00% | 4 | $75.32 | 21.65 | $53.67 | $5.41 | $18.83 | ||
| 16 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000080 | IMPERIAL TOBACCO du Maurier Smooth RG 25 25UN | 71.10% | 4.00% | 12.00% | 3 | $56.16 | 16.23 | $39.93 | $5.41 | $18.72 | ||
| 17 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000080 | IMPERIAL TOBACCO du Maurier Smooth RG 25 25UN | 71.10% | 4.00% | 12.00% | 4 | $74.88 | 21.64 | $53.24 | $5.41 | $18.72 | ||
| 18 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000080 | IMPERIAL TOBACCO du Maurier Smooth RG 25 25UN | 71.10% | 4.00% | 12.00% | 2 | $37.44 | 10.82 | $26.62 | $5.41 | $18.72 | ||
| 19 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 70.86% | 4.00% | 12.00% | 12 | $222.88 | 64.95 | $157.93 | $5.41 | $18.57 | ||
| 20 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 70.78% | 4.00% | 12.00% | 18 | $333.44 | 97.42 | $236.02 | $5.41 | $18.52 | ||
| 21 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 70.78% | 4.00% | 12.00% | 9 | $166.72 | 48.71 | $118.01 | $5.41 | $18.52 | ||
| 22 | 1706-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000095 | IMPERIAL TOBACCO du Maurier Distinct Smooth KS 25 25UN | 70.74% | 4.00% | 12.00% | 16 | $296.00 | 86.6 | $209.40 | $5.41 | $18.50 | ||
| 23 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930006059 | IMPERIAL TOBACCO PALL MALL TUBES 1x200 1x200UN | 64.80% | 4.00% | 12.00% | 1 | $6.25 | 2.2 | $4.05 | $2.20 | $6.25 | ||
| 24 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930006059 | IMPERIAL TOBACCO PALL MALL TUBES 1x200 1x200UN | 64.80% | 4.00% | 12.00% | 1 | $6.25 | 2.2 | $4.05 | $2.20 | $6.25 | ||
| 25 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930006059 | IMPERIAL TOBACCO PALL MALL TUBES 1x200 1x200UN | 64.80% | 4.00% | 12.00% | 1 | $6.25 | 2.2 | $4.05 | $2.20 | $6.25 | ||
| 26 | 1782-Yorkton | Saskatchewan | Shaun Dunn | ######## | TOBACCO | ITCO | 5930000077 | IMPERIAL TOBACCO John Player Rich KS 20 20UN | 63.90% | 4.00% | 12.00% | 1 | $13.49 | 4.87 | $8.62 | $4.87 | $13.49 | ||
| 27 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930052171 | IMPERIAL TOBACCO PETER JACKSON TUBES 1UN | 52.48% | 4.00% | 12.00% | 1 | $6.25 | 2.97 | $3.28 | $2.97 | $6.25 | ||
| 28 | 1716-Winnipeg | Manitoba | Shaun Dunn | ######## | TOBACCO | ITCO | 5930052171 | IMPERIAL TOBACCO PETER JACKSON TUBES 1UN | 52.48% | 4.00% | 12.00% | 1 | $6.25 | 2.97 | $3.28 | $2.97 | $6.25 | ||
Export | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P2:P28 | P2 | =N2/L2 |
| Q2:Q28 | Q2 | =M2/L2 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A2:Q25 | Expression | ="P2<>(AVERAGEIF(P:P.(G2:G1000000=G2:G1000000))" | text | NO |
