AverageIf Function giving unexpected result

[GREGRACH]

Hi All,

I have the following formula: =AVERAGEIF(E9:E134,"<>"&TODAY(),F9:F134)

It is taking information from the following:

Date Received....Date Invoiced (E)...Claim Life (F)
1 March............14 March .............. 13
1 March............6 March............... 5
1 Feb................14 March.............. 41
1 Jan................10 March.............68

So the formula is supposed to give me the average claim life of the above rows in bold text... (dates of 14 March in column E are using the formula =TODAY() so anything with today's date is excluded )... but the formula is calculating 0.6 instead of 36.5. Please note, claim life (column F) is also a formula =DATEDIF(D9,E9,"d") so I'm not sure if that has anything to do with it?

Where am I going wrong? I have a similar formula that works fine but this one does not....

Any help would be hugely appreciated! Thank you.

 

[FDibbins]

Grasping at straws now. Instead of using TODAY(), put a date in it's own cell, then reference that as the criteria

 

[StephenCrump]

One way to fix the problem will be to change the formula in F9 (and copy down) to:

=IF(D9="","",E9-D9)

That will get rid of the zero values filling the rest of column F, and diluting the average.

 

[liveinhope]

another way to fix this is to use AVERAGIFS instead of AVERAGEIF ( excel 2007 onwards on if you're on 2003 or earlier function not available)

=AVERAGEIFS(F9:F134,E9:E134,"<>"&TODAY(),E9:E134,">=1") ... only includes rows where value in col E is not today and is greater or equal to 1 (that's the date of 1/1/900 ... earliest date you can have in date format in excel)

it wont pick up the rows that have "0" in F due to formula being dragged down but without matching data in the date columns

note that the "column to average" comes first in AVERAGEIFS followed by pairs of criteria-range and criteria wile AVERAGIF is the other way round

Date Received	Date Invoiced (E)	Claim Life (F)
1-Mar	14-Mar	13			36.5
1-Mar	6-Mar	5
1-Feb	14-Mar	41
1-Jan	10-Mar	68
		0
		0
		0
		0
		0
		0
		0

<tbody>
</tbody>