These formulas use the structured references available in Excel Tables. The formula in E5 subtracts the mileage in the row above from mileage in the current row to calculate distance (miles driven) since the last gas stop: The SUM formula is used only to prevent errors on the first row, where the row above contains a text value. Without SUM, the first row formula will return the #VALUE error. The SUM function however will treat the text in C4 as zero and prevent the error. The formula in F5 calculates MPG (miles per gallon) by dividing miles driven by gallons used: The result is formatted as a number with one decimal place.
Summary stats
The formulas in I4:I6 calculate best, worst, and average MPG like this: Because we are using an Excel Table to hold the data, these formulas will continue to show correct results as more data is added to the table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
