AVERAGEIF formula question

[tdlsaint]

Hi!
I am trying to figure this formula out. I am currently running this formula:

=AVERAGE('Therapy Pool'!AQ151,'Therapy Pool'!AD151,'Therapy Pool'!Q151,'Therapy Pool'!D151)

But, I would really like it to be :
=AVERAGEIF('Therapy Pool'!AQ151,'Therapy Pool'!AD151,'Therapy Pool'!Q151,'Therapy Pool'!D151,"<>0")

hoping it would average these non-adjacent columns, and ignoring the zeros.

However, when I enter that, I get an error. I am using this to populate date from the fields as listed from one page, onto another.

Thanks!
Pat

[Shareez Saleem]

Assume you have the below information and you need to do average of values where the date is 1-Jan-2017 and value should be >0.

Date Value
1-Jan-17 14
1-Jan-17 0
1-Jan-17 27
1-Jan-17 14
1-Jan-17 16
2-Jan-17 13
3-Jan-17 30
4-Jan-17 18
5-Jan-17 12
1-Jan-17 20
1-Jan-17 28
8-Jan-17 11

=AVERAGEIFS(B:B,A:A,$A$2,B:B,"<>0")
Result: 19.83

=AVERAGEIF(A2:A13,$A$2,B2:B13)
Result: 17

[Glenn Kennedy]

Since your data are in non-contiguous cells, that approach will NOT work. In the attached sheet, you can see the following formula:

=SUM(C3,E3,G3,I3,K3)/INDEX(FREQUENCY((C3,E3,G3,I3,K3),0),2)

Doing exactly what you want. Adjust the ranges to suit your own sheet.

[Richard Buttrey]

...alternatively you could use a Pivot Table and filter out the zero values.

[tdlsaint]

Thank you very much! How would I wrap an IFERROR to display 0.00 instead of #DIV/0!

[AliGW]

Have a look at posts #3, #4 and #5 here: https://www.excelforum.com/excel-for...xt-record.html

[63falcondude]

Thank you very much! How would I wrap an IFERROR to display 0.00 instead of #DIV/0!

If you are referring to the formula from post #3, that would look like this:

=IFERROR(SUM(C3,E3,G3,I3,K3)/INDEX(FREQUENCY((C3,E3,G3,I3,K3),0),2),0)

[Jonmo1]

I would look for some kind of identifying key in another row to indicate which columns should be included.
So instead of saying, do columns D Q AD and AQ
Say, do the columns that have the word "Amount" in row 1 (or whatever value you can find to use as that key).

So you can then write
=AVERAGEIFS('Therapy Pool'D151:AQ151,'Therapy Pool'D1:AQ1,"Amount",'Therapy Pool'D151:AQ151,"<>0")

[tdlsaint]

If you are referring to the formula from post #3, that would look like this:

=IFERROR(SUM(C3,E3,G3,I3,K3)/INDEX(FREQUENCY((C3,E3,G3,I3,K3),0),2),0)

Awesome, thank you very much, I looked at AliGW's suggestions as well and came up with
=IFERROR(SUM('Therapy Pool'!AQ156,'Therapy Pool'!AD156,'Therapy Pool'!Q156,'Therapy Pool'!D156)/INDEX(FREQUENCY(('Therapy Pool'!AQ156,'Therapy Pool'!AD156,'Therapy Pool'!Q156,'Therapy Pool'!D156),0),2),"")

So it displays blank.

Thank you all for your help, it is very much appreciated!

[63falcondude]

Awesome, thank you very much, I looked at AliGW's suggestions as well and came up with
=IFERROR(SUM('Therapy Pool'!AQ156,'Therapy Pool'!AD156,'Therapy Pool'!Q156,'Therapy Pool'!D156)/INDEX(FREQUENCY(('Therapy Pool'!AQ156,'Therapy Pool'!AD156,'Therapy Pool'!Q156,'Therapy Pool'!D156),0),2),"")

So it displays blank.

Thank you all for your help, it is very much appreciated!

You're welcome. Glad we could help.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.