Forum Discussion
Solved
Least common value in a range with blank cells
I have written a formula to find the least common value in a column however it doesn't work when blank cells are in the column, so I was wondering how to get around this? This is my formula: =...
Try this.
=INDEX(AF9:AF32,MATCH(AGGREGATE(15,6,1/(1/COUNTIFS(AF9:AF32,AF9:AF32)),1),COUNTIFS(AF9:AF32,AF9:AF32),0))Edit: Shortened the formula a bit.
There is a little error in you formula:
=IFERROR(INDEX(AF9:AF32,MATCH(MIN(COUNTIF(AF9:AF32,AF9:AF32)),COUNTIF(AF9:AF32,AF9:AF32),0)),"No Least Common")
Detlef_LewinThanks, I have made those changes which has made an improvement.
However I've tested it out and the output is "0" when I include blank cells, rather than displaying the least common value of the cells that have values in them
Try this.
=INDEX(AF9:AF32,MATCH(AGGREGATE(15,6,1/(1/COUNTIFS(AF9:AF32,AF9:AF32)),1),COUNTIFS(AF9:AF32,AF9:AF32),0))Edit: Shortened the formula a bit.