Excel Supreme
Search

Project track · Project 3 of 4

Wine cellar dashboard

Value a cellar, then find which region, vintage and price are carrying it.

Intermediate

The brief

  1. The file

    wine.xlsx holds one sheet, Cellar, with twelve wines in six columns. The columns are the wine, its region, its vintage, how many bottles are on the shelf, the price of one bottle and the score a critic gave it. Rows 15 to 18 are a computed block: bottles held, stock value, mean score and best score.

  2. The work

    The owner is deciding what to restock, and the answer is never the average. It is which region, which vintage and which price are carrying the cellar. Eight tasks run from reading the block to grouping the rows yourself. The preview shows prices and scores exactly as the file does; the download is there when you want to sort twelve rows rather than squint at them.

  3. What good looks like

    Every answer here is one fact, spelled the way the sheet spells it. Where a number needs rounding the task says to how many decimals, because the sheet rounds to two and an unrounded mean would never match.

wine.xlsx · Cellar

screen 1 of 8 · answer in the bar

How many bottles does the cellar hold in total? B15 states it.

Download the dataset (.xlsx) It opens in Excel, Sheets or LibreOffice.

Preview the sheet
ABCDEF
1WineRegionVintageBottlesPriceScore
2Barolo RiservaPiedmont2016247896
3BarbarescoPiedmont2018185493
4Chianti ClassicoTuscany2019602189
5BrunelloTuscany2015129597
6Rioja Gran ReservaRioja2014364294
7AlbariñoRías Baixas2021481888
8PrioratCatalonia2017156695
9Cava BrutCatalonia2019721285
10Mosel RieslingMosel2020302492
11BaroloPiedmont2012811098
12ChablisBurgundy2018403891
13SancerreLoire2021542690
14
15Bottles417
16Stock value13998
17Mean score92.33
18Best score98

The sheet computes B15 =SUM(D2:D13), B16 =SUMPRODUCT(D2:D13,E2:E13), B17 =ROUND(AVERAGE(F2:F13),2), B18 =MAX(F2:F13)

What it does

SUM counts bottles because bottles are one column. Stock value cannot use SUM: it needs each row's bottles multiplied by that row's price and then added, which is what SUMPRODUCT does in a single cell. A helper column of twelve multiplications would say the same thing in twelve cells.

AVERAGE of the score column, rounded to two decimals, gives the cellar's mean critic score, and MAX names the best bottle in it. The tasks then leave the block behind: the interesting questions are which region carries the cellar and which rows are behind the best score.

Syntax

=SUM(D2:D13), =SUMPRODUCT(D2:D13,E2:E13), =ROUND(AVERAGE(F2:F13),2)

Worked example

Common errors

ErrorWhat it means and how to fix it
SUM instead of SUMPRODUCT=SUM(D2:D13,E2:E13) adds the two columns together as one list. It returns 1001, not the value of the stock.
The block inside the rangeThe cellar stops at row 13. A range that reaches row 18 will add the block's own answers into the total.

At work

Stock, price and rating columns are the shape of every inventory sheet a business runs on. The difference between a list and a dashboard is whether someone can answer 'what is this worth, and which part of it is doing the work' without opening a calculator.