AverageIf QUERY...

[GUIDO22]

Hi All
I have two columns. Column A shows the placing : 1,2 or 3....this repeats with every set of 3 rows.... 1,2,3,1,2,3,1,2,3,1,2,3 etc.

Column D is a TRUE /FALSE Value.... for each 'placed set' of 1/2/3 above, only one of these will be TRUE....

Essentially, I would like a formula to be able to determine the AVERAGE number of TRUEs for each placing position 1,2,3.......

For example :
1 : TRUE
2: FALSE
3: FALSE
1: FALSE
2: TRUE
3: FALSE
1: TRUE
2: FALSE
3: FALSE

Avg 1st Place position: 0.66%
Avg 2nd Place position : 0.33%
Avg 3rd Place position : 0%


Thank you in advance for any suggestions.

 

[Brianwarnock]

I don't think you can use average ifs here , I think that you are going to need to do 4 counts, three countifs to get he number of True with each position and a count of all the True, then do the maths.

Brian

 

[lemo]

something like this should work -

=COUNTIFS(A$2:A$10,1,D$2$10,TRUE)/COUNTIF(A$2:A$10,1)

 

[lemo]

wait, what's that green smiley doing there?!
you get the idea...

 

[Brianwarnock]

wait, what's that green smiley doing there?!
you get the idea...

A frequent problem when quoting cell references is a green smiley, to avoid this problem you need to turn smilies off Disable smilies in text is in additional options below the message pane.

Brian

 

[lemo]

got it thanks..