Informazioni sull’esercizio
Un set di dati di 5 righe su un foglio, 2 celle di risposta, circa 8 minuti. Correzione nel tuo browser: il valore e la formula che lo produce, con indicazioni su cosa sistemare. L’esercizio include anche 3 domande a risposta multipla, valutate con lo stesso pulsante Verifica.
Come risolverlo
- C2
=B2/MIN($B$2:$B$6)-1 - F3
=(MAX(B2:B6)-F2)/(MIN(B2:B6)-F2)
Harbor's score is only 2.8% above Station's (=B2/MIN($B$2:$B$6)-1), but on an axis that starts at 95 a column only shows the part of its value above 95, so Harbor's 3.6 points against Station's 0.9 draw a column 4.0 times as tall (F3). With the axis from 0 the drawn heights are the real values and the ratio falls to 1.03. The fix is to type 0 as the axis Minimum; a non-zero axis is acceptable for lines and dots, which show values by position rather than by length.
In Excel, right-click the vertical axis → Format Axis → Axis Options → Bounds and type 0 in Minimum (the same pane on Windows and Mac, and in Excel 2016–2021). When a bound is on Auto, Excel chooses it: if the smallest value is above about five sixths of the largest, the automatic minimum is not 0, so five scores between 95.9 and 98.6 get a cut axis even if nobody set one, and Reset only returns to that automatic value. The picture shows a 300% difference where the data has 2.8%; Tufte called the ratio of the two the lie factor. When small differences matter, chart them directly: columns of each store's gap to the lowest store or to a target, a dot plot, or a line with a clearly labelled axis. Pitfall: =B2/MIN(B2:B6)-1 without $ slides the MIN range down as you fill, so the stores below Station are compared with a different "lowest" score.
Altri metodi validi
- =B2/MIN(B$2:B$6)-1 — locking the rows is enough when you only fill down
- =(B2-MIN($B$2:$B$6))/MIN($B$2:$B$6) — the same, written as (score − lowest) ÷ lowest
- =(MAX(B2:B6)-$F$2)/(MIN(B2:B6)-$F$2) in F3 — the same with F2 locked
Risposte alle domande
Domanda 1. On the slide (chart A), Harbor's column towers over Station's. How much higher is Harbor's score really? Use your column C.
Risposta: About 3% higher
Harbor scores 98.6 and Station 95.9, so Harbor is 98.6 ÷ 95.9 − 1 ≈ 2.8% higher. On the slide each column only shows the part above 95: 3.6 points against 0.9, which draws Harbor's column 4.0 times as tall.
Domanda 2. You select the slide's vertical axis and open Format Axis (right-click the axis → Format Axis). Which change makes the columns honest?
Risposta: Type 0 in the Minimum box
Columns show a value by their length from the axis, so the axis must start at 0 for the lengths to compare. Reset is not enough: when every value is above about five sixths of the largest one, as here, Excel's automatic minimum is not 0 either. Data labels print the true numbers but leave the misleading lengths.
Domanda 3. In which of these charts is a value axis that does not start at 0 acceptable? Select all that apply.
Risposte: A line chart of a share price moving between $95 and $99 e A scatter chart of house size against sale price
Columns and bars show a value by their length, so a cut axis turns small gaps into big ones. Lines and dots show a value by its position, so their axis may zoom into the range where the values move, as long as its labels show where it starts.
Errori comuni
Filling =B2/MIN(B2:B6)-1 down without $: in the rows below Station the MIN range no longer includes Station's score, so those stores are measured against the wrong lowest store.
Come valutiamo
È accettata qualsiasi formula che produce il risultato giusto, a meno che l’attività non indichi una funzione: in quel caso un’altra funzione vale metà dei punti. Confrontiamo i valori con una piccola tolleranza e ignoriamo maiuscole, minuscole e spazi nel testo. Un numero corretto digitato a mano invece di una formula vale metà dei punti. Le attività su più celle valgono il punteggio pieno con il 100% delle celle giuste, metà dei punti dal 50%. Dove serve, cambiamo anche una cella di input su una copia del tuo foglio e controlliamo che la formula si aggiorni.
Risposte calcolate con le regole di Excel 365 e verificate nel nostro correttore in data 20 novembre 2026. Autore: GridBelt team.