Averageif with multiple cells

[kumara_faith]

Hi,

I have the following table:

Book1
	A	B	C
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1,500
4	1/1/2023	Texas
5	1/1/2023	Texas	1,354
6	1/1/2023	Texas	1,478
7	1/1/2023	Texas
8			1,444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1,500
13	1/1/2023	New York	1,500
14	1/1/2023	New York
15	1/1/2023	New York	1,478
16	1/1/2023	New York	1,500
17			1,495
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26			0
27
28	Table 4
29	1/1/2023	Summary
Sheet1

Cell Formulas
Range	Formula
C8,C26,C17	C8	=IFERROR(AVERAGEIF(C3:C7,">0",C3:C7),0)

Table 1-3 are the input tables. In Table 4,in cell C29, I am trying to calculate the average of cell C8, C17 and C26. The average should ignore the cell if the cell is blank or zero. In the above, the correct average is 1469. I tried using averageifs but I am not sure how to nest all the different cells in the formula. I appreciate any help.

 

[jasonb75]

Try

=LET(a,VSTACK(C8,C18,C26),AVERAGE(IF(a,a)))

 

[kumara_faith]

Hi Jason,

Thank you for the solution. The excel version in my organization currently does not have VSTACK formula. Is there an alternative?

 

[Alex Blakenburg]

Just be aware that you are taking an average of an average and that is mathematically incorrect.

 

[kumara_faith]

Hi Alex,

I understand and we will review the formula for Table 1-3. Is there an alternative to Jason formula above since we currently don't have VSTACK ?

 

[shift-del]

Hi

I would suggest this setup with ONE table and a pivot table.

Mappe7
	F	G	H	I	J	K
1	Date	Branch	Revenue		Branch	Average of Revenue
2	01.01.2023	Texas	1500		Iowa
3	01.01.2023	Texas			New York	1494,5
4	01.01.2023	Texas	1354		Texas	1444
5	01.01.2023	Texas	1478		Total	1472,857143
6	01.01.2023	Texas
7	01.01.2023	New York	1500
8	01.01.2023	New York	1500
9	01.01.2023	New York
10	01.01.2023	New York	1478
11	01.01.2023	New York	1500
12	01.01.2023	Iowa
13	01.01.2023	Iowa
14	01.01.2023	Iowa
15	01.01.2023	Iowa
16	01.01.2023	Iowa
Tabelle1

 

[Alex Blakenburg]

This would give you the same result as @shift-del and be a correct average.

=AVERAGE(
       FILTER($C$2:$C$26,($C$2:$C$26<>"") * ISNUMBER($C$2:$C$26) * ISNUMBER($A$2:$A$26),""))

If you want to average the averages (not advised) then this should work.

=AVERAGE(
       FILTER($C$2:$C$26,($C$2:$C$26<>"") * ($C$2:$C$26<>0) * ($A$2:$A$26 = ""),""))

 

[kumara_faith]

Hi Alex,

Thank you for the solution. I didn't provide the exact data layout because I thought column A would not be used. My actual data layout is as below:

Book1
	A	B	C
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1,500
4	1/1/2023	Texas
5	1/1/2023	Texas	1,354
6	1/1/2023	Texas	1,478
7	1/1/2023	Texas
8	1/1/2023	Texas	1,444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1,500
13	1/1/2023	New York	1,500
14	1/1/2023	New York
15	1/1/2023	New York	1,478
16	1/1/2023	New York	1,500
17	1/1/2023	New York	1,495
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26	1/1/2023	Iowa	0
27
28	Table 4
29	1/1/2023	Summary	1325
Sheet1

Cell Formulas
Range	Formula
C8,C26,C17	C8	=IFERROR(AVERAGEIF(C3:C7,">0",C3:C7),0)
C29	C29	=AVERAGE(FILTER($C$2:$C$26,($C$2:$C$26<>"") * ISNUMBER($C$2:$C$26) * ISNUMBER($A$2:$A$26),""))

Is there anyway to modify the formula to derive the correct result using your suggestion which is not to average the average and therefore the correct result should be 1473?

 

[James006]

Hi,

You can test

=AVERAGEIFS(C8:C26,C8:C26,"<>0",A8:A26,"=")

 

[kumara_faith]

Hi,

I am getting the result as follows:

Book1
	A	B	C
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1,500
4	1/1/2023	Texas
5	1/1/2023	Texas	1,354
6	1/1/2023	Texas	1,478
7	1/1/2023	Texas
8	1/1/2023	Texas	1,444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1,500
13	1/1/2023	New York	1,500
14	1/1/2023	New York
15	1/1/2023	New York	1,478
16	1/1/2023	New York	1,500
17	1/1/2023	New York	1,495
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26	1/1/2023	Iowa	0
27
28	Table 4
29	1/1/2023	Summary	#DIV/0!
Sheet1

Cell Formulas
Range	Formula
C8,C26,C17	C8	=IFERROR(AVERAGEIF(C3:C7,">0",C3:C7),0)
C29	C29	=AVERAGEIFS(C8:C26,C8:C26,"<>0",A8:A26,"=")