Need help with AVERAGEIF formula

leopardhawk

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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
257​
[/td][td]
$ 3.22 ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 2018[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
208​
[/td][td]
$ 3.11 ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 2019[/td][/tr][/table]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
257​
[/td][td]
$ - ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 2020[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
257​
[/td][td]
$ - ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 2021[/td][/tr][/table]

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
leopardhawk said:
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
[/TD]
[TD]
$ 3.22 ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: 2018[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]208[/COLOR]​
[/TD]
[TD]
$ 3.11 ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: 2019[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
[/TD]
[TD]
$ - ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: 2020[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
[/TD]
[TD]
$ - ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: 2021[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
Click to expand...


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
You must log in or register to reply here.

Similar threads

E
  • Question Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
317
ExcelenteNovel
E
Y
  • Question Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
486
Yaeger38
Y
k3yn0t3
  • Solved
INDIRECT with INDEX(MATCH())?
2
Replies
10
Views
1K
k3yn0t3
k3yn0t3
ChrisMac1
  • Solved
Formula - Combine exclusions in a search
2
Replies
12
Views
305
Fluff
Fluff
C
  • Solved
Help with Countif and Index Match
Replies
3
Views
646
Fluff
Fluff

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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