Avg number of selections per condition

Leah Schmid

Hi there,

I need some help. I'm trying to calculate the avg number of issues per condition in our master tracker. The issues are in a multi-value cell {Rev Rec Master Tracker Range 1} while the condition is a single-value cell in {Rev Rec Master Tracker Range 2} with values either as "Backlog" or "Net New". I'd like to find out the average # of issues in Backlog and average # of issues in Net New.

Here is what I have which I know isn't calculating the fields correctly. In my example for backlog there are 3 issues noted for 2 items in the backlog. So the avg should be 3/2 = 1.5

=IF(HAS({Rev Rec Master Tracker Range 2}, "Backlog"), COUNTM(HAS({Rev Rec Master Tracker Range 1}, {Rev Rec Master Tracker Range 2}, "Backlog"), 0)) / COUNTIF({Rev Rec Master Tracker Range 2}, "Backlog")

Appreciate any help!

Thanks!

Paul Newcome

So for "Backlog" you would want it to count 3 Action Plan Notes and then divide by 2 "Backlog" entries? If so, try this:

=COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = "Backlog")) / COUNTIFS({RR Type}, @cell = "Backlog")

Paul Newcome

Are you able to provide a screenshot for context?

Leah Schmid

Sure! Here you go.

Paul Newcome

So for "Backlog" you would want it to count 3 Action Plan Notes and then divide by 2 "Backlog" entries? If so, try this:

=COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = "Backlog")) / COUNTIFS({RR Type}, @cell = "Backlog")

Leah Schmid

That worked! Thank you very much, Paul! :)

Paul Newcome

Happy to help. 👍️