Google sheets count occurrences as fractions

Question

Have a column that contains text separated by a ',' and I am trying to work out the fraction of occurrences for each string in the column as detailed below

    a,b
    a
    g,g,f
    a
    b
    b,a,f,f

Output needed in another column

0.5
1
0.33
1
1
0.25

Not sure the best approach here is to use a query and SQL do this? I'm sure there is a simple way?

My current thinking =QUERY(IF D3 contains "," (len(D3)-len(SUBSTITUTE(D3,",","")) else 1))

Answer 1

• SPLIT by ,

• Divide 1 by COUNT of the resulting array

  =1/COUNTA(SPLIT(A2,","))
  

Answer 2

Assuming your data starting in D3, try

=arrayformula(if(len(D3:D), trunc(1/len(substitute(D3:D, ",",)),2),))

and see if that works? (Change range to suit)