I have several sheets that have a value of a crypto currency on each sheet and I am trying to find the max value and then print the name of the coin with that maximum value. But if one of the values of F5 is empty on any sheet the formula won't pick up the maximum or any of the LARGE placings behind. How can I get the formula (below) to ignore an empty F5?
In the cells I am trying to print the name:
=INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,ADA:XRP!A5)&"</b></a>","//b"),MATCH(1,FREQUENCY(MAX(ADA:XRP!F5),ADA:XRP!F5),0))
There are several sheets in between ADA and XRP and A5 has the name of the coin associated.
In previous efforts, this has worked by moving the affected sheet out of the range of ADA:XRP. But in this case now, the max comes up as the next highest in the sheet to the left of the maximum if one of the sheets has a blank F5. I tried the same with LARGE (2) and the same issue.
How do I fix this?
In the cells I am trying to print the name:
=INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,ADA:XRP!A5)&"</b></a>","//b"),MATCH(1,FREQUENCY(MAX(ADA:XRP!F5),ADA:XRP!F5),0))
There are several sheets in between ADA and XRP and A5 has the name of the coin associated.
In previous efforts, this has worked by moving the affected sheet out of the range of ADA:XRP. But in this case now, the max comes up as the next highest in the sheet to the left of the maximum if one of the sheets has a blank F5. I tried the same with LARGE (2) and the same issue.
How do I fix this?
