Forum Discussion

AHegan's avatar
AHegan
Copper Contributor
Sep 22, 2024

Formula not working

I have this formula for excel that gives me the most common value in a range.

The problem I have is that if a cell in the range is blank it becomes NA. This is the formula =@INDEX(K5:AF5,MODE(MATCH(K5:AF5,K5:AF5,0)))

What I need is for the formula to work even if there is a cell with no value in it. And it possible it would be preferable if it only considered the text not number fields but I expect I would need to select each cell rather than have a range?

 

 

excel
Formulas and Functions

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Sep 22, 2024

    AHegan 

    Another option.

    =LET(
a,FILTER(K5:AF5,ISTEXT(K5:AF5)),
b,INDEX(a,MODE.MULT(XMATCH(a,a))),
b)
  • Harun24HR's avatar
    Harun24HR
    Silver Contributor
    Sep 22, 2024

    AHegan Use a TOROW() function before lookup_value.

     

    =@INDEX(K5:AF5,MODE(MATCH(TOROW(K5:AF5,1),K5:AF5,0)))

     

    Alternatively you can use XLOOKUP() like.

     

     

    =LET(x,K5:AF5,y,COUNTIFS(x,x),XLOOKUP(MAX(y),y,x))