Explanation
In this example, the goal is to test if a numeric value is a whole number. There are several ways to go about this. One of the easiest ways is to use the MOD function with a divisor of 1. Any whole number divided by 1 will result in a remainder of zero:
=MOD(5,1)=0 // whole numbers return zero
Any decimal number will have a remainder equal to the decimal portion of the number:
=MOD(5.25,1)=0.25
Therefore, we can simply compare the result to zero with a logical expression that returns TRUE or FALSE:
=MOD(5,1)=0 // returns TRUE
=MOD(5.5,1)=0 // returns FALSE
This is the approach taken in the worksheet as shown, where the formula in C5 is:
=MOD(B5,1)=0
At each row in the data, the formula returns TRUE for whole numbers only.
INT or TRUNC
Another way to solve the problem is with the INT function or the TRUNC function . In this approach, we run the value through one of these functions and compare the result to the original value. If the values match, we know we have a whole number. The formulas look like this:
=A1=INT(A1)
=A1=TRUNC(A1)
Both of these formulas compare the original value in A1 to the same value after removing the decimal portion of the number (if any). Both formulas work fine, but note they behave differently with negative decimal values. For example, if A1 contains -5.5:
=A1=INT(A1)
=-5.5=INT(-5.5)
=-5.5=-6 // returns FALSE
whereas:
=A1=TRUNC(A1)
=-5.5=TRUNC(-5.5)
=-5.5=-5 // returns FALSE
In short, the TRUNC function actually removes the decimal portion of a number, while the INT function always rounds the number down to the next whole value. This matters for negative values, because they are rounded away from zero (i.e. they become more negative). That said, it doesn’t make a difference in this example. INT still returns the correct result for negative decimal numbers because the integer changes and the result of the comparison is always FALSE.
Explanation
Note: this example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all data in an Excel Table called “data” to illustrate how Tables can make some formulas easier to maintain.
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:
[@Mileage]-SUM(C4)
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:
=[@Distance]/[@Gallons]
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:
=MAX(data[MPG]) // best
=MIN(data[MPG]) // worst
=SUM(data[Distance])/SUM(data[Gallons]) // average
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.
