I´m trying to do average IFS in a table, averaging 2 columns but can´t do it. Pls help.

delfinpedro

New Member
Joined
Sep 24, 2018
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Good day, Im a little experienced with tables and have no issues with single sum or average ranges. Even dynamic, etc. Now I´m trying to average 2 columns based on criteria:
=AVERAGEIFS(AMR[[#All],[2001Jan]:[2002Feb]],AMR[[#All],[Act]],[@Act]) It´s returning #VALUE!
I have no issues with single column average range, =AVERAGEIFS(AMR[[#All],[2002Feb]:[2002Feb]],AMR[[#All],[Act]],[@Act])
Once I now how to have 2 columns I{ll include more criteria and include variables. So pls use averageifS I have similar issue with sumifs by the way.
Thx a lot.

1613682510633.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
You have to make 2 formula's and then teh average of this two formula's: average(average(...),average(...))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
You're getting the #VALUE error because the value range (AMR[[#All],[2001Feb]:[2002Feb]]) is not the same size as the criteria range (AMR[[#All],[Act]]). The first is two columns, the next is 1 column.

Averaging 2 averages is not mathematically sound. In this case, where the number of items in each average is the same, then it works. But it's not a good habit to get into, and there's an easier way to do it anyway.

Book1
ABCDEFGHIJ
1Act2001Jan2002Feb2003MarActAverage
2ac226486661208568838Ac443847
3Pas129535012685101275926Pas1281930
4
Sheet2
Cell Formulas
RangeFormula
I2:I3I2=SUMPRODUCT(AMR[[2001Jan]:[2002Feb]]*(AMR[Act]=[@Act]))/SUMPRODUCT(SIGN(AMR[[2001Jan]:[2002Feb]])*(AMR[Act]=[@Act]))


The equivalent version of SUMIFS would not have the second SUMPRODUCT on the formula.


Edit: I just realized the use of SIGN to get the count only works if all the values are positive. If that's not the case, let me know and I'll come up with something else.
 

delfinpedro

New Member
Joined
Sep 24, 2018
Messages
15
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You're getting the #VALUE error because the value range (AMR[[#All],[2001Feb]:[2002Feb]]) is not the same size as the criteria range (AMR[[#All],[Act]]). The first is two columns, the next is 1 column.

Averaging 2 averages is not mathematically sound. In this case, where the number of items in each average is the same, then it works. But it's not a good habit to get into, and there's an easier way to do it anyway.

Book1
ABCDEFGHIJ
1Act2001Jan2002Feb2003MarActAverage
2ac226486661208568838Ac443847
3Pas129535012685101275926Pas1281930
4
Sheet2
Cell Formulas
RangeFormula
I2:I3I2=SUMPRODUCT(AMR[[2001Jan]:[2002Feb]]*(AMR[Act]=[@Act]))/SUMPRODUCT(SIGN(AMR[[2001Jan]:[2002Feb]])*(AMR[Act]=[@Act]))


The equivalent version of SUMIFS would not have the second SUMPRODUCT on the formula.


Edit: I just realized the use of SIGN to get the count only works if all the values are positive. If that's not the case, let me know and I'll come up with something else.
Thank you this combined with Mart 37´s contribution gave me the answer:) After this I included several indirects, etc, to automatice;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,082
Messages
5,639,967
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top