How to get the average?

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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
QRSTU
1MonthsSalesMonthsSales
2Jan15Jan15
3Feb36Feb
4Mar50Mar50
5Average33,7Average32,5
Omzet
Cell Formulas
RangeFormula
R5,U5R5=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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try this semi colon version,
Excel Formula:
=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:
Excel Formula:
=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)}, "<>", "")
 
Upvote 0
Try something like this:
Bascially check to see if the value is blank and make it blank for the average calculation.

Book3
ABCD
83535
93939
10 
11
1237
1337
Sheet2
Cell Formulas
RangeFormula
D8:D10D8=IF(B8="","",B8)
D12D12=AVERAGE(D8,D9,D10)
D13D13=AVERAGE(D8,D9,D10)
 
Upvote 0
Try this semi colon version,
Excel Formula:
=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:
Excel Formula:
=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.
 
Upvote 0
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?
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top