Is this possible?!

sammielou · #13 02-25-2016, 09:08 AM

Quote:

Originally Posted by gebobs

I'll dip my toe in again with a possible solution. If there's a more elegant one, perhaps xor knows it.

I don't think one can do AVERAGEIFS over multiple ranges. If so, then you would have to do it for each column of data and then combine. But averages of averages may not be accurate. This is definitely the case here since there are variable counts in the columns which would weight the percentages in columns with fewer counts greater than those with more.

Thus, the most accurate solution I can think of would be to go back to my earlier less elegant formula and expand it over the columns i.e. (sum of sums for each)/(sum of counts for each).

Without further ado, the formula from hell...

Code:

=IFERROR((SUMIF(D2:D13,"*ave",C2:C13)+SUMIF(F2:F13,"*ave",E2:E13)+SUMIF(H2:H13,"*ave",G2:G13)+SUMIF(J2:J13,"*ave",I2:I13)+SUMIF(L2:L13,"*ave",K2:K13)+SUMIF(N2:N13,"*ave",M2:M13)+SUMIF(P2:P13,"*ave",O2:O13)+SUMIF(R2:R13,"*ave",Q2:Q13))/(COUNTIF(D2:D13,"*ave")+COUNTIF(F2:F13,"*ave")+COUNTIF(H2:H13,"*ave")+COUNTIF(J2:J13,"*ave")+COUNTIF(L2:L13,"*ave")+COUNTIF(N2:N13,"*ave")+COUNTIF(P2:P13,"*ave")+COUNTIF(R2:R13,"*ave")),"")

Thanks so much for the formula. I tried it out and it doesn't seem to be accurate it is a few percentile off of the actual average. I think we may try to do something to make this work by pasting them in the same column. Unless anyone has a better solution...