I want to find a formula to find the mode of the highlighted cells in excel

Question

I am trying to find a formula to find the mode of the highlighted cells. The value inside cell D13 will determine the number of highlighted cells. If it is 3, 3 rows will be highlighted starting from cell H3. For example, if the highlighted cell contains 3 values B, B, and A. B will be returned to cell C13 as the mode value. I have attached a screenshot of my Excel, anyone can help me?

Answer 1

"I want to return the most occurrence alphabet."

You could use:

=LET(r,TAKE(H3:H10,D13),@SORTBY(r,COUNTIF(r,r),-1))

Answer 2

You can do this with conditional formatting. For each cell in column H, comparing the matching value in the Row column (A) with the value in $D$13. If Row value is <= $D$13 then format background colour. In my sheet cell C9 is a named range 'Select'.

Answer 3

One thing you should realise about conditional formatting: it reads the value of a cell and as a result of that, it alters a formatting (like the background fill, which looks like highlighting).
I have the impression that you want to do just the opposite: read the formatting (like the highlighting) and as a result of that, change a value.

This is not the way conditional formatting works, and if this is indeed what you need, you might be forced to look for a VBA solution for that.

Answer 4

Give a try to the following formula.

=LET(
x,INDEX(H3:H10,SEQUENCE(D13)),
y,UNIQUE(x),
z,MAP(y,LAMBDA(t,SUM(--(x=t)))),
TAKE(SORTBY(y,z,-1),1)
)