It occurs to me that my formula only works for number 1.333 through 9.999. I
don't think it would calculate correctly for numbers 9

Dave
--
Brevity is the soul of wit.


"Lara Shook" wrote:

Thank you, dave f and toppers, very much. I made a formula that converts it.
It's a little simpler than Dave's, but a little longer than toppers. I was
just hoping I could format it to APPEAR with the .1 or .2, without having to
actually convert it, because I'm getting tired of converting back and forth
(depending on what I'm calculating, and the source of my data). Just FYI,
here's mine: TRUNC(a1)+(a1-TRUNC(a1))/3*10

"Dave F" wrote:

What does the MOD function do?
--
Brevity is the soul of wit.


"Toppers" wrote:

Try:

=ROUND(INT(A1)+(MOD(A1,1)/0.33333)*0.1,1)

"Dave F" wrote:

Try this formula in a helper column to convert 1.333 to 1.1, etc.:

=IF(A1-0.333=INT(A1),VALUE(CONCATENATE(LEFT(A1,1),".1")), IF(A1-0.666=INT(A1),VALUE(CONCATENATE(LEFT(A1,1),".2")), ROUNDUP(A1,1)))

How does it work? Well, INT(A1) = 1 if A1 = 1.333. VALUE(CONCATENATE(...))
returns a value of a text string concatenation.

Finally, if A1=1.999 it will round up to 2.

Dave
--
Brevity is the soul of wit.


"Lara Shook" wrote:

I'm sure all you baseball fans have got an easy solution to this one:

I've got fractions of innings pitched as .333333, or .6666667. I want them
to appear in the report as .1 or .2. So far, I've been converting them back
and forth, one for the report, and the other for calculations. I would sure
rather just change the FORMATTING, so that the cell just shows a .1 or .2,
but still retains its original calculated innings pitched. How do I do that?
180.6666667 would APPEAR on the report as 180.2
Thanks!
I'm using Excel 2003