jtencha
asked on
Custom number ("Tape measure" )format for Excel 2003
I want to use Excel 2003 to draft up a preliminary floor plan for a room. I want to add dimensions on the
length and width of the perimeter of the layout such that the numbers are displayed as follows (in inches):
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11, 2.0, 2.1, 2.2.......3.0...etc
As you can see, for every 12 inches, the number resets back to the next whole one foot measurement.
I need to be able to enter the first number, and then have the capability to click and drag the numbers across the screen so that excel will automatically fill the numbers in this format for me.
thanks
length and width of the perimeter of the layout such that the numbers are displayed as follows (in inches):
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11, 2.0, 2.1, 2.2.......3.0...etc
As you can see, for every 12 inches, the number resets back to the next whole one foot measurement.
I need to be able to enter the first number, and then have the capability to click and drag the numbers across the screen so that excel will automatically fill the numbers in this format for me.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not sure if this will be useful, but if you have the numbers in a column or row (maybe hidden on the sheet, you could use the following formula to translate the number to the style you like.
=IF(QUOTIENT(B10,12)<1, B10, QUOTIENT(B10,12) & "." & MOD(B10,12))
This will turn the cell b10 into the following:
b10=25 then formula cell = 2.1
b10=11 then formula = 11
b10=13 then formula = 1.1
=IF(QUOTIENT(B10,12)<1, B10, QUOTIENT(B10,12) & "." & MOD(B10,12))
This will turn the cell b10 into the following:
b10=25 then formula cell = 2.1
b10=11 then formula = 11
b10=13 then formula = 1.1
I can produce the exact series requested with this formula:
=SUBSTITUTE(SUBSTITUTE(TEX
I can also produce a series with a leading decimal point for values less than 1 foot with:
=SUBSTITUTE(SUBSTITUTE(TEX
Results of the two formulas are shown below:
Inches 0 1 2 3 4 5 6 7 8 9 10 11 12 13 21 22 23 24 25
Formula 1 0 1 2 3 4 5 6 7 8 9 10 11 1.0 1.1 1.9 1.10 1.11 2.0 2.1
Formula 2 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 0.10 0.11 1.0 1.1 1.9 1.10 1.11 2.0 2.1
Brad
=SUBSTITUTE(SUBSTITUTE(TEX
I can also produce a series with a leading decimal point for values less than 1 foot with:
=SUBSTITUTE(SUBSTITUTE(TEX
Results of the two formulas are shown below:
Inches 0 1 2 3 4 5 6 7 8 9 10 11 12 13 21 22 23 24 25
Formula 1 0 1 2 3 4 5 6 7 8 9 10 11 1.0 1.1 1.9 1.10 1.11 2.0 2.1
Formula 2 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 0.10 0.11 1.0 1.1 1.9 1.10 1.11 2.0 2.1
Brad
What Brad provided is a formula to convert inches to the inches/feet value you want. But it doesn't work as a "drag" solution.
This formula works as a sequence generator. To use it, put the first measurement into a cell after formatting that cell as text. Place this formula in an adjacent cell:
=SUBSTITUTE(SUBSTITUTE(TEX
changing the reference "A1" to the cell containing the first measurement. Then drag or copy the formula as far down or to the right as desired.
Kevin
This formula works as a sequence generator. To use it, put the first measurement into a cell after formatting that cell as text. Place this formula in an adjacent cell:
=SUBSTITUTE(SUBSTITUTE(TEX
changing the reference "A1" to the cell containing the first measurement. Then drag or copy the formula as far down or to the right as desired.
Kevin
To ensure this question does not get deleted, I'm objecting to the cleanup action now that two additional possible solutions have been posted.
Kevin
Kevin
1 1/12
1 2/12
1 3/12
...
2
2 1/12
Use the custom number format "# ??/12" to do this.
Kevin