Display the rounded-down value but retain the actual value in Excel

Hello,

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:

    =11/4

which displays the result:

    2.75

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.

Thanks
WeThotUWasAToadAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
I think this is not possible. Round is possible but round down for display-only is not.
0
 
CompProbSolvConnect With a Mentor Commented:
Can you put the rounded-down value in a separate column?  Not as "pretty", I know, but would get you what you want.
0
 
WeThotUWasAToadAuthor Commented:
Thanks for the comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.