Need help with AVERAGEIF formula

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, this formula below is working but it is also including data for 2020 and 2021 which are both currently 'blank'.

Code:
=AVERAGE('2018'!I257,'2019'!I208,'2020'!I257,'2021'!I257)

I tried the following and I am getting an error ("You've entered too many arguments for this function."). I just want it to ignore 2020 and 2021 until there is data there.
Any ideas?

Code:
=AVERAGEIF('2018'!I257,'2019'!I208,'2020'!I257,'2021'!I257,"<>0")

Cheers!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Where is your range of cells to average?
And with so many criteria, you have to use =AverageIfs() I believe
Which will need your average range, and then criteria range, criteria, criteria 2 range, criteria2, etc...

Hope that helps
 
Upvote 0
As far as I can tell from research, you cannot averageif in a non-contiguous range. Meaning they all have to be next to each other. You can average in a non-contiguous range, but not with criteria. From what I've read, it is possible to do if you adjust your data, for instance add a new column at the end of the sheet and put an x in each cell, that way you can grab an entire range for each column, and say sum if or average if there is an "x" in column z

Sorry that's not much help
 
Upvote 0
Try:

=AVERAGE('2018:2021'!I257)

But the cells in 2020 and 2021 must be blank.
 
Upvote 0
DanteAmor, the cells in 2020 and 2021 are blank but it is including them in the AVERAGE, thereby skewing the result. Here are the first two cells, 2018 and 2019. Note also that on the 2019 sheet, the target cell is I208.

Excel 2016 (Windows) 32 bit
I
257
$ 3.22
Sheet: 2018

Excel 2016 (Windows) 32 bit
I
208
$ 3.11
Sheet: 2019

I should be getting an AVERAGE of $3.16 but, because 2020 and 2021 are blank (see below), I am getting an AVERAGE of $1.58.

Excel 2016 (Windows) 32 bit
I
257
$ -
Sheet: 2020

Excel 2016 (Windows) 32 bit
I
257
$ -
Sheet: 2021

Thanks!
 
Upvote 0
Okay, this works... bit convoluted, but it works...

Code:
=('2018'!I257+'2019'!I208+'2020'!I257+'2021'!I257) / (('2018'!I257<>0)+('2019'!I208<>0)+('2020'!I257<>0)+('2021'!I257<>0))

Cheers!
 
Upvote 0
DanteAmor, the cells in 2020 and 2021 are blank but it is including them in the AVERAGE, thereby skewing the result. Here are the first two cells, 2018 and 2019. Note also that on the 2019 sheet, the target cell is I208.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
$ 3.22

<tbody>
</tbody>
Sheet: 2018

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]208[/COLOR]​
$ 3.11

<tbody>
</tbody>
Sheet: 2019

<tbody>
</tbody>

I should be getting an AVERAGE of $3.16 but, because 2020 and 2021 are blank (see below), I am getting an AVERAGE of $1.58.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
$ -

<tbody>
</tbody>
Sheet: 2020

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
$ -

<tbody>
</tbody>
Sheet: 2021

<tbody>
</tbody>

Thanks!


The cell must be empty. I guess you have a formula, if the result of the formula is 0, then you must change the result of the formula to ""
For example:


=IF(SUM(I252:I255)=0,"",SUM(I252:I255))
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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