Finding Maximum/Minimum fraction between two columns

[kiwi44]

Hi all!

I currently have two columns, each with fractions in them. They are visual acuities for the right eye and the left eye (the fraction that shows how good someone can see in each eye...e.g., 20/20, 20/10, etc.). I am trying to find a way to enter the maximum between the two cells into a third column, and the minimum into a a fourth column. In other words, I want to get the following results from A1 and B1:

A1 = 20/30 , B1 = 20/70, C1 = 20/30, D1 = 20/30


I tried using this formula: =MAX(A1,B1), but it is not working. I have about 1500 rows to do this for, so I'd appreciate any help! Thanks =)

 

[CyrusTheVirus]

What do you want your answer to be for cell C1?
What do you want your answer to be for cell D1?

B/c you say you want the maximum in C1, and minimum in D1, but both have the same answer?

 

[kiwi44]

So sorry! C1 = 20/30 (maximum). D1 = 20/70 (minimum)

 

[CyrusTheVirus]

Hi kiwi,

Please test the below before using. Thanks.

C1:

=IF((LEFT($A1,2)/RIGHT($A1,2))>(LEFT($B1,2)/RIGHT($B1,2)),$A1,$B1)

D1:

=IF((LEFT($A1,2)/RIGHT($A1,2))<(LEFT($B1,2)/RIGHT($B1,2)),$A1,$B1)

 

[kiwi44]

Perfect, thanks so much. Just one question. I used A1/B1 for simplicities sake. My column headers are actually there, so this will be starting in A2. Every time you have ($A1,2) written, would that become ($A2,2)?

 

[kiwi44]

Actually, this formula also isn't working for all the values.

For instance, in a row with 20/100 in both cells, the "C1" formula give an error of "#DIV/0!"

In another case, in a row with A1 = 20/20, B = 20/25, the "C1" formula gives 20/25, which is the not maximum.

 

[CyrusTheVirus]

Hi kiwi,

Well, first off I have a question for you that I forgot to ask the first time around...

... Will all of your criteria be two numbers then a division sign and then another two numbers... In other words, will you ever have have something like 1/05 or 111/55 or 1/180 or 5/4, if so, then we'll need to adjust your formula. Because The current formula works only if your numbers will always be two numbers and then a division sign and then another two numbers (ex: 20/30 or 40/50, it WON'T work for 2/30 or 40/5). So, we should probably sort that out first, perhaps it's not an issue...


Regarding your follow up question. If your column headers are there, then in cell C2 put

=IF((LEFT($A2,2)/RIGHT($A2,2))>(LEFT($B2,2)/RIGHT($B2,2)),$A2,$B2)

and in cell D2 put

=IF((LEFT($A2,2)/RIGHT($A2,2))<(LEFT($B2,2)/RIGHT($B2,2)),$A2,$B2)

 

[kiwi44]

The numerator will always be 20. The denominator will either be 2 or 3 digits, depending on the cell. E.g., 20/30 or 20/400.

I ended up doing that for the formula, but I'm still having the same problems that I mentioned with my second follow up post:

"For instance, in a row with 20/100 in both cells, the "C1" formula give an error of "#DIV/0!"

In another case, in a row with A1 = 20/20, B = 20/25, the "C1" formula gives 20/25, which is the not maximum."

 

[CyrusTheVirus]

Hi kiwi,

Ok. I'm going to bed now and will check your results tomorrow, but try the below. Hope this helps (please test the formula, I'm very sleepy!).

C2:

=IF((20/RIGHT($A2,LEN($A2)-SEARCH("/",$A2)))>(20/RIGHT($B2,LEN($B2)-SEARCH("/",$B2))),$A2,$B2)

D2:

=IF((20/RIGHT($A2,LEN($A2)-SEARCH("/",$A2)))<(20/RIGHT($B2,LEN($B2)-SEARCH("/",$B2))),$A2,$B2)

 

[CyrusTheVirus]

Hi kiwi,

Unless someone has a better idea, then perhaps the below is a bit more concise?

C2:

=IF((20/MID($A2,4,3))>(20/MID($B2,4,3)),$A2,$B2)

D2:

=IF((20/MID($A2,4,3))<(20/MID($B2,4,3)),$A2,$B2)