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

 

[snjpverma]

Try this semi colon version,

=AVERAGEIF({GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)}; "<>"; "")

Or this comma version:

=AVERAGEIF({GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)}, "<>", "")

 

[awoohaw]

Try something like this:
Bascially check to see if the value is blank and make it blank for the average calculation.

Book3
	A	B	C	D
8		35		35
9		39		39
10
11
12				37
13				37
Sheet2

Cell Formulas
Range	Formula
D8:D10	D8	=IF(B8="","",B8)
D12	D12	=AVERAGE(D8,D9,D10)
D13	D13	=AVERAGE(D8,D9,D10)

 

[Romano_odK]

Try this semi colon version,

=AVERAGEIF({GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)}; "<>"; "")

Or this comma version:

=AVERAGEIF({GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023);GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)}, "<>", "")

Good morning,
Thank you for your reply. I get the idea, but its not working. Get a "There is a problem with this formula" warning. I use the semi colon version.

 

[snjpverma]

Good morning,
Thank you for your reply. I get the idea, but its not working. Get a "There is a problem with this formula" warning. I use the semi colon version.

Getting same error using both the formulas?

 

[Romano_odK]

Getting same error using both the formulas?

Unfortunately yes.

 

[awoohaw]

it is really hard to debug when you can't share the actual worksheet with the forum. Can you use the xl2bb add (link below) to post a mini of the pivot tables (including the data sources), Anonymized for privacy.

But, as I mentioned earlier try wrapping each GETPIVOT with an IF statement and test for blanks, then return blanks.
If the zeros are counts of NOTHING then test for zero and return a blank ("") - but with this you will never include any legitimate value of ZERO in an average.

=AVERAGE(
IF(GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023));
IF(GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023));
if(GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)))

or


=AVERAGE(
IF(GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023));
IF(GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023));
if(GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)))

 

[Romano_odK]

it is really hard to debug when you can't share the actual worksheet with the forum. Can you use the xl2bb add (link below) to post a mini of the pivot tables (including the data sources), Anonymized for privacy.

But, as I mentioned earlier try wrapping each GETPIVOT with an IF statement and test for blanks, then return blanks.
If the zeros are counts of NOTHING then test for zero and return a blank ("") - but with this you will never include any legitimate value of ZERO in an average.

=AVERAGE(
IF(GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023));
IF(GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023));
if(GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)="","",GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)))

or


=AVERAGE(
IF(GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$24;"afldat";3;"Years";2023));
IF(GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$40;"afldat";3;"Years";2023));
if(GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)=0,"",GETPIVOTDATA("Marge";$B$56;"afldat";3;"Years";2023)))

I understand that this is difficult without the sources. I this case its not easy for me to share the sheet since there are all kinds of pivot tables and sql queries connected to it. It still doesn't work, but like you said its not doable without the source. So thank you for your time and efforts. I will try to find another solution for this.

Have a great day.

Romano

 

[awoohaw]

then try to create a scenario that you can. the GETPIVOT Functions are FORMULAS that return a value.
This is what I tried to do in POST #3 for you, but you haven't replied what happened.

 

[Romano_odK]

then try to create a scenario that you can. the GETPIVOT Functions are FORMULAS that return a value.
This is what I tried to do in POST #3 for you, but you haven't replied what happened.

I saw that and get the idea of it but how do I implement this, so where?