Link to home
Avatar of jtencha
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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
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  


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(TEXT(IF(ISERROR(FIND(".",A1)),(A1+1)/12,LEFT(A1,FIND(".",A1)-1)+(MID(A1,FIND(".",A1)+1,2)+1)/12),"# 0/12"),"/12","")," ",".")

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