formatting for innings pitched

[Guest]

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

 

[Guest]

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

 

[Guest]

Try:

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

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

 

[Guest]

What does the MOD function do?

 

[David Biddulph]

Dave,

Excel has a help facility which will tell you what the various functions do.

 

[Guest]

Help files are inaccessible on this installation.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

In the column to the right of your list enter
=(ROUNDDOWN(A1,0))+(IF(VALUE(RIGHT(A1,1))>5,0.2,0.1)), then hide your
original column so that any formula you have can still the see the proper
value.