# average problem

#### mtsf26

##### Board Regular
 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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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

Last edited:

I guess Averageifs will help

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

Replies
3
Views
164
Replies
2
Views
201
Replies
0
Views
153
Replies
15
Views
296
Replies
2
Views
335

1,207,392
Messages
6,078,219
Members
446,322
Latest member
pebuje

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back