### AverageIf using cell reference instead of hard coded date

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.

1. very helpful article! thanks :))

2. How do i do this, but exclude all 0 values in the "cost" column??

3. How would I amend this if I wanted to add another criteria, e.g. only average if the date fell between 2 dates using <= and >=?

5. I 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)....
I would like to write a formula to calculate the average for each day...can you help me>

6. thank you veru much !!
this 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 :)

7. Glad this example helped. Thanks for stopping by.

8. Thanks 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?

9. This write up helped eliminate my problem within a few seconds. Thanks for the well written explanation.

10. This comment has been removed by the author.

11. this was very helpful..thank you.

12. this was very helpful..thank you.

13. Exactly what I was looking for, thankyou!!

16. And 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.

1. I yet to understand why but criteria like "<<20>" will match "<20".

17. Is 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.

Current 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.

1. I am having the same problem and have not been able to figure this out.

2. Try this format:
=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)

18. Awesome, saved me alot of time on hard coding each value for 100+ cells.

19. Tanya, Min… kalau rangenya berupa sejumlah kolom yang tidak berurutan gimana? Misalnya yang mau di rata-rata itu cel A1, D1, G1, J1, dan M1. Gimana?

20. Thank you so much! You basically saved my life =)

21. Wow! This is the perfect blog I am looking this type of blog its awesome blog here , share great information about this topic. This informative blog helps many readers with their decision-making regarding the situation. Great articles and will look forward for more!
Asian dating