Rounding down and displaying as a fraction

[Dianna Michelle]

I have a calculated number that I want to display to the ½ fraction. The
fraction format rounds up.
Even if I use the rounddown to get the number I want, when I format the cell
to the half fraction, it rounds my number back up again. I’ve tried
increasing the decimal places of the original calculated number and that
doesn’t help.

I’m trying to calculate paid days earned (based on hours worked).

Even if you work enough hours to get 1.49999999999999 days, you don’t get 1
½ days until you cross the 1.5 boundary.

 

[Ron Rosenfeld]

On Fri, 4 Dec 2009 09:09:02 -0800, Dianna Michelle <Dianna

I have a calculated number that I want to display to the ½ fraction. The
fraction format rounds up.
Even if I use the rounddown to get the number I want, when I format the cell
to the half fraction, it rounds my number back up again. I’ve tried
increasing the decimal places of the original calculated number and that
doesn’t help.

I’m trying to calculate paid days earned (based on hours worked).

Even if you work enough hours to get 1.49999999999999 days, you don’t get 1
½ days until you cross the 1.5 boundary.

You did not post your formula, but I suspect you are not using the ROUNDOWN
function properly.

To ROUNDOWN to the nearest 1/2:

=ROUNDDOWN(A1/(1/2),0)*1/2

or, more simply:

=ROUNDDOWN(A1*2,0)/2

You could also use:

=FLOOR(A1,1/2)


In the above, substitute your formula for the calculated number for A1:

=ROUNDDOWN(your_formula*2,0)/2

=FLOOR(your_formula,1/2)

Then, format the cell as a fraction with one number; e.g: # ?/?
--ron

 

[Joe User]

I have a calculated number that I want to display to the ½ fraction.
[....] Even if I use the rounddown to get the number I want, when I
format the cell to the half fraction, it rounds my number back up again.

I am thinking of a solution, but I won't tell you the details. How helpful
is that?

But that's effectively what you wrote: ``I calculate a number, but I won't
tell you what it is or how I calculate it. I tried a work-around using
ROUNDDOWN to get a number that I want, but I won't tell you exactly what I
did or what I want``. How useful is that?

Even if I use the rounddown to get the number I want, when I
format the cell to the half fraction, it rounds my number back
up again.
[....]
Even if you work enough hours to get 1.49999999999999 days,
you don’t get 1 ½ days until you cross the 1.5 boundary.

On the one hand, you seem to be concerned that Fraction As 1/2s rounds the
number (up, in this case). On the other hand, you seem to be concerned that
it failed to, or so you say.

What exactly is your concern? Hint: Provide the details of a numerical
example.

Yes, the Fraction format rounds numbers to the nearest multiple of the
fraction. For example, with Fraction As 1/2s, any number between 1.25 [*]
and 1.74999999999999 is displayed as 1 1/2.

So when I enter the constant 1.49999999999999 into a cell, Fraction As 1/2s
displays 1 1/2 as expected. I do __not__ have to "cross the 1.5 boundary".
That contradicts your observation above, unless you misspoke somehow.

If you are trying to say that you don't want 1 1/2 until the value is 1.5 or
larger, you must round down to the lowest multiple of 1/2 yourself. The
formula is:

=ROUNDDOWN(A1*2,0)/2

But does that address your problem? I really have no idea, since you were
unclear about the problem.


-----
Endnotes

[*] I said that 1.25 is formatted as 1 1/2. But be aware that some numbers
that Excel displays as 1.250...0 to 14 decimal places, are considered to be
less than 1.25 for the Fraction format; for example, =1.25-(2^-52) in A1.
Such numbers cannot be entered as constants, but they can arise as the
result of calculations. Apparently, the Fraction format does an exact
binary comparison, not a comparison of the value as it would be displayed
with 15 significant digits. You can simulate the same exact binary
comparison with the formula =A1-1.25>=0. Note that the formula =A1>=1.25,
which is mathematically equivalent, is not good enough because of Excel
heuristics that try to adjust "infinitesimal" differences.


----- original message -----