How to rank a cell value within the same cell

Question

I am trying to rank a set of numbers (in a column), but I want that rank to appear next to the number in the same cell.

For example, if the number is "21" and that ranks "3rd" – I would want the cell to read "21 (3)". BONUS POINTS if I can format the (3) to be smaller/superscript.

---

WHAT I'VE TRIED: While the cell I am working in already has an equation in it, I will simplify it. Let's say A23 = 15 and A24 = 6. I have tried the following equation but it keeps giving me an error. The cell I am working in is H103. The range for ranking is H103:H114.

=SUM(A23+A24) & "(" & RANK(H103,$H$103:$H$114) & ")"

Even if I remove the parentheses or rework that a bit, I am still left with an error. Just trying to find any way I can have 2 formulas, with different results in the same cell; one of which showing the rank of that particular cell.

HERE IS COPY OF MY SHEET — This is a duplicated Google Sheet, so feel free to play around in there if you'd like. I am focusing on ranking H103:H114.

Answer 1

paste in cell H103:

=ARRAYFORMULA(TEXT(VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!B:C"), 
 "select B,sum(C) where B is not null group by B", 0), 2, 0)+
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!E:D"), 
 "select E,sum(D) where E is not null group by E", 0), 2, 0), "#.00")&" "&CHAR(8317)&
 VLOOKUP(RANK(VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!B:C"), 
 "select B,sum(C) where B is not null group by B", 0), 2, 0)+
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!E:D"), 
 "select E,sum(D) where E is not null group by E", 0), 2, 0), 
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!B:C"), 
 "select B,sum(C) where B is not null group by B", 0), 2, 0)+
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!E:D"), 
 "select E,sum(D) where E is not null group by E", 0), 2, 0), 0),  
 {1,  CHAR(185);             2, CHAR(178);            3, CHAR(179);  
  4,  CHAR(8308);            5, CHAR(8309);           6, CHAR(8310);           
  7,  CHAR(8311);            8, CHAR(8312);           9, CHAR(8313); 
  10, CHAR(185)&CHAR(8304); 11, CHAR(185)&CHAR(185); 12, CHAR(185)&CHAR(178)}, 2, 0)&
 CHAR(8318))

---

paste in cell I103:

=ARRAYFORMULA(VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!B:C"), 
 "select B,sum(C) where B is not null group by B", 0), 2, 0)+
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!E:D"), 
 "select E,sum(D) where E is not null group by E", 0), 2, 0)-C103:C114)

---

paste in cell L103:

=ARRAYFORMULA(VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!B:C"), 
 "select B,sum(C) where B is not null group by B", 0), 2, 0)+
 VLOOKUP(B103:B114, QUERY(INDIRECT($A$100&"!E:D"), 
 "select E,sum(D) where E is not null group by E", 0), 2, 0)-J103:J114)

---

conditional formatting for H column:

red color custom formula:

=REGEXMATCH(H103, CHAR(8317)&CHAR(185)&CHAR(178)&CHAR(8318))

green color custom formula:

=REGEXMATCH(H103, CHAR(8317)&CHAR(185)&CHAR(8318))