formatting for innings pitched
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
|