Over the weekend I received an email from Pam. She was experiencing difficulties using the AverageIf example from a previous posting of mine. Previous Post She wanted to use the average if formula but instead of hard coding a date in the criteria window, she wanted to use a cell reference instead.
In Pam’s example she was trying to use a cell reference in her criteria such as “=b2175” since she has a date in cell b2175. Unfortunately she was receiving a DIV error as Excel did not like the ways she coded her solution.
Let’s help her out today…
In the following example I want to look for dates greater than or equal to 2/19/2012 and perform my average if calculation. I can hard code my date (as in my original posting) in the formula by encompassing it inside of “”>= date “”.
=AVERAGEIF(C2:C10,">=2/19/2012",B2:B10)
However if I want Excel to reference a date in a different cell then I need to modify my formula slightly. The concept is the same but I first place quotes around my test “>=” greater than or equal to. I then add an & and then the cell reference. I end up with ,">="&C7
Now the formula looks for dates greater than the date in cell C7 (2/19/2012)
In cell E2 I have the formula=AVERAGEIF(C2:C10,">="&C7,B2:B10). This references the date in cell C7. Notice that I need to place the “>=” and join that with the &.
A

B

C

D

E

F
 
1

Item

Cost

Sale Date

Number Of Rows That Qualify

Average If

Formula and calculations

2

A

1.00

1/15/2012

7.50

=AVERAGEIF(C2:C10,">=2/19/2012",B2:B10)
 
3

B

2.00

1/22/2012
 
4

C

3.00

1/29/2012

7.50

=AVERAGEIF(C2:C10,">="&C7,B2:B10)
 
5

D

4.00

2/5/2012
 
6

E

5.00

2/12/2012
 
7

F

6.00

2/19/2012

1
 
8

G

7.00

2/26/2012

1
 
9

H

8.00

3/4/2012

1
 
10

I

9.00

3/11/2012

1
 
11

35.00

4

The result is the same as if you hard coded the date into the criteria.
Hope this helps you Pam.
very helpful article! thanks :))
ReplyDeleteHow do i do this, but exclude all 0 values in the "cost" column??
ReplyDeleteHow would I amend this if I wanted to add another criteria, e.g. only average if the date fell between 2 dates using <= and >=?
ReplyDeleteThanks. Your post is really helpful.
ReplyDeleteI have a large data set that collects half hourly data (48 entries each day). The time stamp starts with month year and time ( 1/1/2013 0:00)....
ReplyDeleteI would like to write a formula to calculate the average for each day...can you help me>
thank you veru much !!
ReplyDeletethis is what I have struggeled for hours,
and for some reason didn't look it up.. :(
after seeing your solution it was resolved in seconds :)
Glad this example helped. Thanks for stopping by.
ReplyDeleteThanks so much for this post. I was very frustrated for a while. I was wondering why this syntax works? could you explain or point me to an article that explains what the ampersand does that allows Excel to reference that cell?
ReplyDeleteThis write up helped eliminate my problem within a few seconds. Thanks for the well written explanation.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletethis was very helpful..thank you.
ReplyDeletethis was very helpful..thank you.
ReplyDeleteExactly what I was looking for, thankyou!!
ReplyDeleteYou made my day!
ReplyDeleteThis was extremely helpful!!!!!!!!!!
ReplyDeleteAnd what if the value I want to find is the text value that begins with a comparison operator? "<20" for criteria does not give me values = "<20" it gives me values less than 20.
ReplyDeleteI yet to understand why but criteria like "<<20>" will match "<20".
DeleteIs there something similar for AVERAGEIFS? I would like to do this for a date range instead of one date, but I get #DIV/0 when using your function above.
ReplyDeleteCurrent formula which works:
=AVERAGEIFS(Work!$G:$G,Work!$A:$A,">=6/1/2016",Work!$A:$A,"<=6/30/2016")
Trying what is outlined in the article does not:
=AVERAGEIFS(Work!$G:$G,Work!$A:$A,">=&K10",Work!$A:$A,"<=&L10")
Cell K10 is 6/1/2016, L10 is 6/30/2016, and both are formatted as dates.
I am having the same problem and have not been able to figure this out.
DeleteTry this format:
Delete=AVERAGEIFS('Data for NCR Report (6)'!L:L,'Data for NCR Report (6)'!S:S, ">="&'Weekly NC count'!B3, 'Data for NCR Report (6)'!S:S,"<="&'Weekly NC count'!C4)
Awesome, saved me alot of time on hard coding each value for 100+ cells.
ReplyDeleteTanya, Min… kalau rangenya berupa sejumlah kolom yang tidak berurutan gimana? Misalnya yang mau di ratarata itu cel A1, D1, G1, J1, dan M1. Gimana?
ReplyDeleteThank you so much! You basically saved my life =)
ReplyDelete