Jun 18 2020 12:39 PM
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:
=IFERROR(INDEX(AF9:AF32,MATCH(MIN(COUNTIF(AF9:AF32,AF9:AF32)),COUNTIF(AF9:AF32,AF9:AF32),"No Least Common")),"No Least Common")
Jun 18 2020 02:15 PM
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")
Jun 18 2020 02:34 PM
@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
Jun 18 2020 03:02 PM - edited Jun 18 2020 03:39 PM
SolutionTry 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.
Jun 18 2020 04:32 PM
Jun 18 2020 03:02 PM - edited Jun 18 2020 03:39 PM
SolutionTry 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.