Try this:
=INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1
INDIRECT
ADDRESS 1
ROW
COLUMN
1 The third parameter in ADDRESS() is absolute_relative_mode
[ OPTIONAL - 1 by default ] - An indicator of whether the reference is row/column absolute. 1 is row and column absolute (e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute (e.g. $A1), and 4 is row and column relative (e.g. A1).
Unfortunately filter() unique() and similar are not structured to work on individual cells within a 2 dimensional range, they tend to return rows or columns. The following works as long as you do not skip ranks and every row in the data set is accounted for in the ranking
For this the output goes in A2:C6 and the data is in G2:K6
This formula can be entered into the top cell of the "1st" column, A2, and dragged down the column
=countif($G2:$K2,"="&large($G$2:$K$6,1))
it counts the number of occurrences of the largest number in the data set.
Because largest() does not provide unique values we need to skip past all of the instances of the highest value to get to the second highest. Fortunately we just counted the instances of the highest value by row. We will take the sum() of that data to get us to the second largest unique value.
This formula can be entered into the top cell of the "2nd" column, B2, and dragged down the column
=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:A$6)+1))
The third rank is similar but we need to sum both of the preceding columns:
=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:B$6)+1))
The use of sum() here is a short cut taking advantage of the way you are structuring your output. A version of the 3rd column formula that can exist independently would look like this(using input range of G31:K35):
=countif($G32:$K32,"="&large($G$31:$K$35,countif($G$31:$K$35,">="&large($G$31:$K$35,countif($G$31:$K$35,"="&large($G$31:$K$35,1))+1))+1))
the nesting of countif()'s and large()'s gets deeper and deeper for each successive rank.
https://support.google.com/docs/answer/3093480?hl=en&ref_topic=3105474
Best Answer
For conditional formatting, apply the following to A2: "Custom formula is"
(You can then indicate what the range of formatting should be, e.g., A2:A to get all A column except the header).
To filter the content, use either Query or Filter. Here is an example with
FILTER:If you place this on Sheet 2, it will pick the columns A-E from the rows where the stated conditions hold.
Note that having the same condition enforced in two different places is not future-proof: someone might change the condition in one place and forget to change in the other. I would add another column to Sheet 1, such as "Export":
which will contain TRUE if the row is to be exported. (One can use the
IFstatement to enter custom strings there.) Then both conditional formatting and the filter would use that column.