# Custom number (&quot;Tape measure&quot; )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
zorvek (Kevin Jones)

There is no way to format that kind of number. You can display the mreasurement in fractions of feet:

1 1/12
1 2/12
1 3/12
...
2
2 1/12

Use the custom number format "# ??/12" to do this.

Kevin
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
I can produce the exact series requested with this formula:
=SUBSTITUTE(SUBSTITUTE(TEXT(A1/12,"# 0/12"),"/12","")," ",".")

I can also produce a series with a leading decimal point for values less than 1 foot with:
=SUBSTITUTE(SUBSTITUTE(TEXT(A1/12,") 0/12"),"/12","")," ",".")

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