Solved

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

Posted on 2016-08-29
61 Views
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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 43

Accepted Solution

Saqib Husain, Syed earned 250 total points
ID: 41774850
I think this is not possible. Round is possible but round down for display-only is not.
0

LVL 21

Assisted Solution

CompProbSolv earned 250 total points
ID: 41775035
Can you put the rounded-down value in a separate column?  Not as "pretty", I know, but would get you what you want.
0

Author Closing Comment

ID: 41811396
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month2 days, 9 hours left to enroll