Display the rounded-down value but retain the actual value in Excel
Posted on 2016-08-29
In Excel, what formula will display the rounded-down value in a cell but retain its exact value?
For example, suppose you have the following formula in cell D4:
which displays the result:
Formatting D4 to [Number with 0 decimal places] changes what the cell displays to 3 but retains the cell's actual value of 2.75 (eg for use in further computations).
Applying the function =ROUND(D4,0) results in both the display and the value 3. And applying the function =FLOOR(D4,1) results in both the display and the value 2.
I'm looking for a formula to display the rounded-down value (even when the decimal is >= 0.5) as the =FLOOR() function does but retain the actual value of the cell as is the case with [Number] formatting.