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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

M
  • Question Question
Drop down list with selection containing Alpha and Numeric characters not working
Replies
1
Views
126
myall_blues
myall_blues
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
220
Domenic
D
E
  • Question Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
329
ExcelenteNovel
E
Y
  • Question Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
493
Yaeger38
Y
k3yn0t3
  • Solved
INDIRECT with INDEX(MATCH())?
2
Replies
10
Views
1K
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

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