Average only cells with value

[Goddess]

Hi,

I have a range of cells with values, and am trying to get the average of those cells with values only.

For example, Company ABC has 2 areas, and since both areas only have data till March, I will need to sum the values in both areas and divide it by 3 (Jan, Feb and March). As I will get more data periodically, I may need to divide the total sum by 4, 5, 6 months, etc as and when I receive data. As the list is very long, I tried using pivot table, but it didnt work as pivot table will average it by 12 months. Anyway to work around it?

The end result I need is highlighted in yellow.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Entities	Area	Floor Area (sqm)*	Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Total
2	ABC	Lot 1	5.00	5.00	2.00	6.10										13.10
3	ABC	Lot 2	4.00	2.00	1.30	5.20										8.50
4	ABC															-
5	Sub total			7.00	3.30	11.30	-	-	-	-	-	-	-	-	-	21.60
6	DEF	Lot 1	6.00	8.00	8.40	6.50	2.50	7.80	2.80							36.00
7	DEF															-
8	DEF															-
9	Subtotal			8.00	8.40	6.50	2.50	7.80	2.80	-	-	-	-	-	-	36.00
10	XYZ	Lot 1	3.00	5.10	2.10	8.30	9.00	4.70	2.10							31.30
11	XYZ															-
12	XYZ															-
13	Subtotal			5.10	2.10	8.30	9.00	4.70	2.10	-	-	-	-	-	-	31.30
14
15		Average
16	ABC	7.2
17	DEF	6
18	XYZ	2.22
19
Sheet1

Cell Formulas
Range	Formula
D13:O13,D9:O9,D5:O5	D5	=SUM(D2:D4)
P2:P13	P2	=SUM(D2:O2)

Cells with Data Validation
Cell	Allow	Criteria
D9:O9	Any value
D13:O13	Any value
D2:O5	Any value

Thanks!

 

[Peter_SSs]

How do you arrive at 2.22 for XYZ?

 

[Tom.Jones]

If in B18 is a typo (must be 5.22 NOT 2.22) then:

B16 =AVERAGEIF(D7:O7,">0")
B17 =AVERAGEIF(D11:O11,">0")
B18 =AVERAGEIF(D15:O15,">0")

 

[Goddess]

How do you arrive at 2.22 for XYZ?

sorry.. typo.. should be 5.22

 

[Goddess]

If in B18 is a typo (must be 5.22 NOT 2.22) then:

B16 =AVERAGEIF(D7:O7,">0")
B17 =AVERAGEIF(D11:O11,">0")
B18 =AVERAGEIF(D15:O15,">0")

The thing is... I have a long list. so I might have an error in the formula if I type in the formula line by line. Or is there a way to copy the formula, where it will skip certain number of rows (in this example, skip 3 rows)?

Thanks!

 

[Peter_SSs]

Or is there a way to copy the formula, where it will skip certain number of rows (in this example, skip 3 rows)?

Like this?

22 06 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	ABC	Lot 1	5	5	2	6.1										13.1
3	ABC	Lot 2	4	2	1.3	5.2										8.5
4	ABC															0
5	Sub total			7	3.3	11.3	0	0	0	0	0	0	0	0	0	21.6
6	DEF	Lot 1	6	8	8.4	6.5	2.5	7.8	2.8							36
7	DEF															0
8	DEF															0
9	Subtotal			8	8.4	6.5	2.5	7.8	2.8	0	0	0	0	0	0	36
10	XYZ	Lot 1	3	5.1	2.1	8.3	9	4.7	2.1							31.3
11	XYZ															0
12	XYZ															0
13	Subtotal			5.1	2.1	8.3	9	4.7	2.1	0	0	0	0	0	0	31.3
14
15		Average
16	ABC	7.2
17	DEF	6
18	XYZ	5.216667
Average

Cell Formulas
Range	Formula
D5:O5,D13:O13,D9:O9	D5	=SUM(D2:D4)
P2:P13	P2	=SUM(D2:O2)
B16:B18	B16	=INDEX(P$2:P$13,MATCH(A16,A$2:A$13,0)+3)/COUNTIF(INDEX(D$2:O$13,MATCH(A16,A$2:A$13,0)+3,0),">0")

 

[Goddess]

Thanks Peter! Works like magic

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

A couple of other adjustments that you could consider?
- Change to the green subtotal row formula to only give a sum if numbers exist above.
- Add a new column Q to easily calculate the average for that section
- Allows for a simpler formula to get the averages in the yellow section.

22 06 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Entities	Area	Floor Area (sqm)*	Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Total	Average
2	ABC	Lot 1	5	5	2	6.1										13.1
3	ABC	Lot 2	4	2	1.3	5.2										8.5
4	ABC															0
5	Sub total			7	3.3	11.3										21.6	7.2
6	DEF	Lot 1	6	8	8.4	6.5	2.5	7.8	2.8							36
7	DEF															0
8	DEF															0
9	Subtotal			8	8.4	6.5	2.5	7.8	2.8							36	6
10	XYZ	Lot 1	3	5.1	2.1	8.3	9	4.7	2.1							31.3
11	XYZ															0
12	XYZ															0
13	Subtotal			5.1	2.1	8.3	9	4.7	2.1							31.3	5.216667
14
15		Average
16	ABC	7.2
17	DEF	6
18	XYZ	5.216667
Average (2)

Cell Formulas
Range	Formula
D5:O5,D13:O13,D9:O9	D5	=IF(COUNT(D2:D4),SUM(D2:D4),"")
Q5,Q13,Q9	Q5	=P5/COUNT(D5:O5)
P2:P13	P2	=SUM(D2:O2)
B16:B18	B16	=INDEX(Q$2:Q$13,MATCH(A16,A$2:A$13,0)+3)

 

[Goddess]

You're welcome. Thanks for the confirmation.

A couple of other adjustments that you could consider?
- Change to the green subtotal row formula to only give a sum if numbers exist above.
- Add a new column Q to easily calculate the average for that section
- Allows for a simpler formula to get the averages in the yellow section.

22 06 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Entities	Area	Floor Area (sqm)*	Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Total	Average
2	ABC	Lot 1	5	5	2	6.1										13.1
3	ABC	Lot 2	4	2	1.3	5.2										8.5
4	ABC															0
5	Sub total			7	3.3	11.3										21.6	7.2
6	DEF	Lot 1	6	8	8.4	6.5	2.5	7.8	2.8							36
7	DEF															0
8	DEF															0
9	Subtotal			8	8.4	6.5	2.5	7.8	2.8							36	6
10	XYZ	Lot 1	3	5.1	2.1	8.3	9	4.7	2.1							31.3
11	XYZ															0
12	XYZ															0
13	Subtotal			5.1	2.1	8.3	9	4.7	2.1							31.3	5.216667
14
15		Average
16	ABC	7.2
17	DEF	6
18	XYZ	5.216667
Average (2)

Cell Formulas
Range	Formula
D5:O5,D13:O13,D9:O9	D5	=IF(COUNT(D2:D4),SUM(D2:D4),"")
Q5,Q13,Q9	Q5	=P5/COUNT(D5:O5)
P2:P13	P2	=SUM(D2:O2)
B16:B18	B16	=INDEX(Q$2:Q$13,MATCH(A16,A$2:A$13,0)+3)

Thanks! Will take note of it