| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Error | Excel Worksheet Functions | |||
| difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
| Formatting dates in the future | Excel Worksheet Functions | |||
| Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
