How to get the average?

[Romano_odK]

Good afternoon,

How do I get the average of 3 cells when not all the 3 cells are constantly filled in. For example,

Omzet Kohler groep 2023 1.08.xlsx
	Q	R	S	T	U
1	Months	Sales		Months	Sales
2	Jan	15		Jan	15
3	Feb	36		Feb
4	Mar	50		Mar	50
5	Average	33,7		Average	32,5
Omzet

Cell Formulas
Range	Formula
R5,U5	R5	=AVERAGE(R2:R4)

Here in the second mini table you see that it works, when 1 cell is empty it won't be counted as a divider.

Below is a part of 3 different pivot tables. I try do the same, but it does not work. The total gets devided by 3 and not 2 when a cell is empty.


=AVERAGE(GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023))
Values are

-35,0% -39,0% empty result is -24,7% Which is not good. Should have been 37. Thank you for your time. Romano

 

[awoohaw]

take what I did and post it into your spreadsheet near your formula that isn't working.
Be sure the formula I have works with the data in my mini workbook.
Then insert your formulas into the formulas in D8 and D9 and D10.