Excel Formula Help

888 Views | 8 Replies | Last: 7 yr ago by gibby03
gibby03
How long do you want to ignore this user?
AG
Ok I want to take a set of values from Throw #1 (which is cell B2) and Throw #4 (which is cell E2) and determine the MAX number. I have that setup with no problem. Now, I need in that BEST box to show not only the MAX value but then convert it to Feet and Inches in the format like 32'9".

Suggestions?

Predmid
How long do you want to ignore this user?
AG
add an extra column between each throw and separate out feet & inches.

have the "best" formula be =max(throw1feet+throw1inches/12,throw2feet+throw1inches/12,etc.)
AggieChemE09
How long do you want to ignore this user?
AG
=INT(F1)&"' "&ROUND(MOD(F1,1)*12,0)&""""


or you can just have this in your BEST column



=INT(MAX(B2:E2))&"' "&ROUND(MOD(MAX(B2:E2),1)*12,0)&""""
gibby03
How long do you want to ignore this user?
AG
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".

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.

I have this in Google Docs if someone would be willing to check it out I can give you access and see what you come up with. I like to create these things but then I get into these details I don't know how to fix!! LOL. Anyway, appreciate the help you did give.
Icecream_Ag
How long do you want to ignore this user?
S
I may be doing this the hard way, but each event or person would need their own page

then the main page would have to use the same calc, but reference the relevent page.

=INT(MAX(nameofsheet!B2:E2))&"' "&ROUND(MOD(MAX(nameofsheet!B2:E2),1)*12,0)&""""

I think I'm working that correctly.
Ark03
How long do you want to ignore this user?
AG
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?
gibby03
How long do you want to ignore this user?
AG
Ok,

This first sheet is what I have where I enter each throwers throw in. For example a kid throws 24 ft 3.5 inches.



1. How could I get it to "show" 24' 3.5" in column 1 and then as I enter each throw subsequently? I am open to the easiest solution both how to type it in or what columns to add to get it to do it.
2. I then want it to auto generate the "best" throw in that column.
3. Then I want it to send that "best" throw to my next sheet......

This is the second sheet where I have my total tally from each sheet in the workbook. I have 10 tabs down below all titled and then linked back to this page.



1. How do I get it to find the best throw from all the meets and put it in that "best throw" column?

I know I really struggle with some of this but I think it's worth trying for me.

Right now I am just going to type it in like this.....24.0350. That makes sense for me and I know that means 24 ft 3.5 inches. But, my counterparts may not know what that means and having it "spelled out" for them would make it easier!!
ABATTBQ11
How long do you want to ignore this user?
AG
You need to format the max value for throws if you want to enter it differently than simply typing it out. You can enter everything as text, and max() should still find the highest value.

Using it in another sheet is pretty easy. You just need to specify the sheet name in the reference.

If you can send this to me at username @ gmail, I can set it up for you in a few minutes.
ABATTBQ11
How long do you want to ignore this user?
AG
Nvmd
gibby03
How long do you want to ignore this user?
AG
sent
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.