formatting for innings pitched
 

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

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

formatting for innings pitched
 

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

formatting for innings pitched
 

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

formatting for innings pitched
 

Dave,

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

"Dave F" wrote in message
...
What does the MOD function do?

"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.

"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

formatting for innings pitched
 

Help files are inaccessible on this installation.
--
Brevity is the soul of wit.


"David Biddulph" wrote:

Dave,

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

"Dave F" wrote in message
...
What does the MOD function do?

"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.

"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

formatting for innings pitched
 

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

formatting for innings pitched
 

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

formatting for innings pitched
 

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.

"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