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.

2 comments:

  1. very helpful article! thanks :))

    ReplyDelete
  2. Been looking for this specific answer for a long time. THANK YOU!!!!

    ReplyDelete