average problem

[mtsf26]

	a	b	c	d	e
1	21-6-12	24-6-12	25-6-12	26-6-12	=AVERAGE((DATEDIF(A1,B1,"d")),(DATEDIF(A1,C1,"d")),(DATEDIF(A1,D1,"d")))
2

<tbody>
</tbody>

hi all,
i have problem with this result actually. i want to get the average of this date, but if then one of column have no data, it will not give any result. maybe you guys have the solution for this problem?

 

[ZabutonNage]

Hi there,

It seems you are looking for the average difference in days compared to the date in column A. Is that correct?
If so, this formula should do the trick. It actually calculates the differences against the smallest date no matter how many dates you have entered. However, you need at least two dates, otherwise it returns #DIV/0!.

= ( SUM(A1:D1) - COUNTA(A1:D1) * MIN(A1:D1) ) / (COUNTA(A1:D1) - 1)

Dates are stored as numerical values, that's why you can just sum them up like this. Then you subtract the smallest value times the number of not empty cells. This is then divided by the number of not empty cells minus 1 to get the average difference in days. (Minus 1 because 4 dates have 3 differences in your case)

Don't forget to adjust the cell format if required.

Hope this helps

 

[vlady]

how about averageifs also.

 

[VishMishra]

Vlady is right...

I guess Averageifs will help

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2])