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.

23 comments:

  1. very helpful article! thanks :))

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

    ReplyDelete
  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 >=?

    ReplyDelete
  4. Thanks. Your post is really helpful.

    ReplyDelete
  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>

    ReplyDelete
  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 :)

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

    ReplyDelete
  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?

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

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Exactly what I was looking for, thankyou!!

    ReplyDelete
  12. This was extremely helpful!!!!!!!!!!

    ReplyDelete
  13. 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.

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

      Delete
  14. 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.

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

      Delete
    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)

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

    ReplyDelete
  16. 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?

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

    ReplyDelete