• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
I think this is not possible. Round is possible but round down for display-only is not.
0
 
CompProbSolvCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now