gibby03 said:
Ok. So I used some of those formulas that you guys suggested, AggieChemE09 yours did exactly what I wanted but then I realized two problems.
1. When I did that, I didn't figure out how to type in a number and have it convert to feet and inches. In other words if someone threw 24 feet and 10.5 inches I didn't know how to put that in the spreadsheet so that it could convert it to 24'10.5".
Is there a reason you don't want to type 24'10.5"?
There are several other ways to do this.
You could input the total in inches, then have a formula that converts it to feet and inches
Quote:
=INT(A1/12)&"'"&MOD(A1,12)&""""
If you input 298.5 in A1, the above formula would return 24'10.5"
Alternatively, you could input feet and inches in separate columns, then have a formula put them together
Quote:
=CONCATENATE(A1,"'",B1,"""")
Input 24 in A1 and 10.5 in B1, and the formula would return 24'10.5"
Once you do this, you'd want a formula to break this back down to something excel can read so it could be ranked. The easiest way to do this would be to refer back to your original input (ie the total number or inches you converted, or the feet and inches in cells A1 and B1, but if you want a formula to convert 24'10.5" back to inches that could be done, in theory.
I am the master at creating necessarily complicated formulas, but this one will convert anything in the feet'inches" format back to inches, regardless of the number of characters in the feet or inches place. You do have to have a placeholder in each place, so if it's 5 feet 0 inches you have to type 5'0" in A1
Quote:
=LEFT(A1,FIND("'",A1)-1)*12+LEFT(RIGHT(A1,LEN(A1)-FIND("'",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("'",A1)))-1)
If you input 24'10.5" in A1 it will return 298.5
If you input 5'0" in A1 it will return 60
If you input 5'8" in A1 it will return 68
I'm sure there is a better way to do this, but someone else can clean it up.
Quote:
2. I have it linked to a main page that compiles all the results from the entire year and when I entered that formula it wouldn't "read" it on that main page.
What's the formula that is not reading data on the main page?