Need help with AVERAGEIF formula

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try:

=AVERAGE('2018:2021'!I257)

But the cells in 2020 and 2021 must be blank.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,312
Messages
5,635,491
Members
416,860
Latest member
coen078

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
Top