Hi Folks.
With the current economics regarding pricing on foods, our canteen staff have asked me if there was a way I could set up a spreadsheet with a list of suppliers in column format so they could enter a price for particular items and it rank it in terms of price across the list of supplier - cheapest to dearest and colour coded from Red to Green so they can see at a glance what has changed.
I was thinking along the lines of using the Rank function but I'm not sure how to set it up to compare the 4 suppliers in this mock setup.
Basically they would like if a 'price' changes in any of the supplier, it is compared with the price in the other 3 suppliers and if its the cheapest, it goes Green. If it's the dearest, it goes Red and if it's in between, its in a different shade of yellow.
Hope this makes sense.
Regards
Declan
With the current economics regarding pricing on foods, our canteen staff have asked me if there was a way I could set up a spreadsheet with a list of suppliers in column format so they could enter a price for particular items and it rank it in terms of price across the list of supplier - cheapest to dearest and colour coded from Red to Green so they can see at a glance what has changed.
I was thinking along the lines of using the Rank function but I'm not sure how to set it up to compare the 4 suppliers in this mock setup.
Basically they would like if a 'price' changes in any of the supplier, it is compared with the price in the other 3 suppliers and if its the cheapest, it goes Green. If it's the dearest, it goes Red and if it's in between, its in a different shade of yellow.
Hope this makes sense.
Regards
Declan
| Canteen Supplier Price Comparison.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | Supplier 1 | Supplier 2 | Supplier 3 | Supplier 4 | |||||||||||||||||||||||
| 2 | Item Description | Rank | Quantity | Weight (gms) | Price | Unit Price | Rank | Quantity | Weight (gms) | Price | Unit Price | Rank | Quantity | Weight (gms) | Price | Unit Price | Rank | Quantity | Weight (gms) | Price | Unit Price | ||||||
| 3 | Brakes Garlic & Parsley Bread Slices | 85 | 24 | £8.00 | £0.09 | 85 | 24 | £7.00 | £0.08 | 85 | 24 | £9.00 | £0.11 | 85 | 24 | £6.00 | £0.07 | ||||||||||
Sheet1 | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3,X3,R3,L3 | F3 | =E3/C3 |
