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.