AVERAGEIFS help

[RodneyC]

My formula will go in cell B16068 (and will be dragged down to subsequent cells). It will look for the value in $A16068 in the range $A$3:$A$16063

I’m starting with this =AVERAGEIFS($A16068,$A$3:$A$16063, and that's where I get stuck.

From there I need it to look in $E$3:$P$16063 where the value of the cell is greater than 0.

For every row A3:A16063 where the value in A16068 is found, look in E3:P16063, if the value is greater than 0, add them up and divide by how many there are to give me the average.

Thanks in advance.

 

[jakel27]

Hi Rodney,

Try =AVERAGEIFS($A$3:$A$16063,$E$3:$P$16063,">0")

 

[RodneyC]

Hi Rodney,

Try =AVERAGEIFS($A$3:$A$16063,$E$3:$P$16063,">0")

Thanks for the idea. That produced a #Value! error

 

[jakel27]

Oh sorry, I just re-read the question and it's much more complex than I thought.
It sounds like this would require VBA to do a loop and search through each row for a clean method. I'm not the best with VBA, so hopefully someone with better VBA experience can help, or maybe someone else does have a way to do this using functions.

I have made a way of doing what you asked, but it requires more cells and maybe something impractical for your size.

Row 2 has the values you have in your column A. So basically Columns Q, S and U in my example check if the rows contain your values. Then Columns R, T and V do the AverageIFS.

Cell Q3: =IF(COUNTIF($E3:$P3,$Q$2),AVERAGEIFS($E3:$P3,$E3:$P3,">0"))
Cell Q7: =AVERAGE(Q$3:Q$6)

Could rearrange cells accordingly, to make it neater but just requires shifting cells in code.

View attachment 66509

This probably won't help, but may give you an idea of how to adapt it into your sheet.

Someone with better Functions specialties or VBA should be able to do a better job.

Good luck!

 

[RodneyC]

Oh sorry, I just re-read the question and it's much more complex than I thought.
It sounds like this would require VBA to do a loop and search through each row for a clean method. I'm not the best with VBA, so hopefully someone with better VBA experience can help, or maybe someone else does have a way to do this using functions.

I have made a way of doing what you asked, but it requires more cells and maybe something impractical for your size.

Row 2 has the values you have in your column A. So basically Columns Q, S and U in my example check if the rows contain your values. Then Columns R, T and V do the AverageIFS.

Cell Q3: =IF(COUNTIF($E3:$P3,$R$2),"yes","no")
Cell R3: =IF($Q3="yes",AVERAGEIFS($E3:$P3,$E3:$P3,">0"))

Could rearrange cells accordingly, to make it neater but just requires shifting cells in code.

View attachment 66506

This probably won't help, but may give you an idea of how to adapt it into your sheet.

Someone with better Functions specialties or VBA should be able to do a better job.

Good luck!

I think I can work with this. I'll dive in deeper tomorrow. Just got handed another project that will take me away from this one for a bit

 

[jakel27]

Yeah, sorry I edited my answer and changed the code a bit to make it simpler and should look like so. But your column A could be spread across horizontally like Row 3 to make it easier to look at. Or change code, so you're reading from column A such as...

Cell Q3: =IF(COUNTIF($E3:$P3,$Q$2),AVERAGEIFS($E3:$P3,$E3:$P3,">0"))
Cell Q7: =AVERAGE(Q$3:Q$6)

Good luck