=AVERAGE() vs avoiding empty cells

[msword]

I am creating the "currency exchange" sheet for 2023. The problem is that I am getting the "#DIV/O!" error for future monthes (e.g. December) or any empty monthes in the past (e.g. January). Therefore, the =AVERAGE() exchange rate for the whole year 2023 is also became broken with the "#DIV/O!" error automatically.
The solution to that might be to avoid empty cells during =AVERAGE() calculation, but how to achieve that?

 

[dreid1011]

I am creating the "currency exchange" sheet for 2023. The problem is that I am getting the "#DIV/O!" error for future monthes (e.g. December) or any empty monthes in the past (e.g. January). Therefore, the =AVERAGE() exchange rate for the whole year 2023 is also became broken with the "#DIV/O!" error automatically.
The solution to that might be to avoid empty cells during =AVERAGE() calculation, but how to achieve that?

You can use =AGGREGATE instead. =AGGREGATE(1,6,range)

The 1 declares the AVARAGE function and the 6 ignores error values within the range.

 

[msword]

You can use =AGGREGATE instead. =AGGREGATE(1,6,range)

The 1 declares the AVARAGE function and the 6 ignores error values within the range.

The same "#DIV/O!" error is produced after applying =AGGREGATE(1,6,M26:M31)

 

[dreid1011]

The same "#DIV/O!" error is produced after applying =AGGREGATE(1,6,M26:M31)

Could you provide a sample of your data then and the formula producing the error? I tested my suggestion by forcing a #DIV/0! error and it worked as expected.

 

[Taul]

Have you tried to exclude the zeros in the average calculation by using AVERAGEIF

=AVERAGEIF(A1:A10,"<>0")

 

[dreid1011]

Have you tried to exclude the zeros in the average calculation by using AVERAGEIF

=AVERAGEIF(A1:A10,"<>0")

I believe the issue is the #DIV/0! error resulting from underlying formulas within the Average range are causing the Average function to also return an error, which AverageIf also falls victim to. Not sure why the Aggregate function is not working for them.

 

[Phuoc]

1. You can add IFERROR to the monthly average formula:

=IFERROR(AVERAGE(...),"")

2. Or use the annual average formula:

=AVERAGE(IF(ISNUMBER(Range),Range,""))

 

[msword]

Have you tried to exclude the zeros in the average calculation by using AVERAGEIF

=AVERAGEIF(A1:A10,"<>0")

Same #DIV/O! error.

I believe the issue is the #DIV/0! error resulting from underlying formulas within the Average range are causing the Average function to also return an error, which AverageIf also falls victim to. Not sure why the Aggregate function is not working for them.

=AGGREGATE(1,6,M26:M31)
All cell in M26:M31 in the beyond example are empty. No values. No formulas.

1. You can add IFERROR to the monthly average formula:

=IFERROR(AVERAGE(...),"")

Its just filling the cell with what i put in between the double quotes (e.g. "1"). I don't understand how it could help.

2. Or use the annual average formula:

=AVERAGE(IF(ISNUMBER(Range),Range,""))

ISNUMBER has 2 ranges and a quotes. why? I have only single month/range. breaks my noobie excel mind.

here is my example:

example.xlsx
	B	C	D	E	F
2	averages
3		€	$
4	2023	#DIV/0!	#DIV/0!
5
6	JAN	39.62
7	FEB	#DIV/0!
8	MAR	#DIV/0!
9	APR	#DIV/0!
10	MAY	#DIV/0!
11	JUN	#DIV/0!
12	JUL	#DIV/0!
13	AUG	41.5
14	SEP	#DIV/0!
15	OCT	#DIV/0!
16	NOV	#DIV/0!
17	DEC	#DIV/0!
18
19	rates €
20	JAN	39.5	39.35		40
21	FEB
22	MAR
23	APR
24	MAY
25	JUN
26	JUL
27	AUG	41		42
28	SEP
29	OCT
30	NOV
31	DEC
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=AVERAGE(C6:C17)
C6:C17	C6	=AVERAGE(C20:F20)

 

[MARK858]

Aggregate works fine for me in C4, D4 amended for no numbers in the range.

Book1.xlsb
	A	B	C	D	E	F
1
2		averages
3			€	$
4		2023	40.5583333
5
6		JAN	39.61666667
7		FEB	#DIV/0!
8		MAR	#DIV/0!
9		APR	#DIV/0!
10		MAY	#DIV/0!
11		JUN	#DIV/0!
12		JUL	#DIV/0!
13		AUG	41.5
14		SEP	#DIV/0!
15		OCT	#DIV/0!
16		NOV	#DIV/0!
17		DEC	#DIV/0!
18
19		rates €
20		JAN	39.5	39.35		40
21		FEB
22		MAR
23		APR
24		MAY
25		JUN
26		JUL
27		AUG	41		42
28		SEP
29		OCT
30		NOV
31		DEC
Sheet1

Cell Formulas
Range	Formula
C4	C4	=AGGREGATE(1,6,C6:C17)
D4	D4	=IF(SUM(D6:D17)=0,"",AGGREGATE(1,6,D6:D17))
C6:C17	C6	=AVERAGE(C20:F20)