Averageif and array or averageif and sumproduct

[Tennisguuy]

In column B I have the date of various accounts. In row s I have the following formula to calculate the number of each account has been open =If(B5="","",abs(B5-Today())) and I have the formula copied do to row 400.

I do an average in cell S1 for the number of days with the following formula =averageif(S5:S400,">0") because otherwise excel will include the blanks cells in the average.

Instead of having a formula in rows S5:S400 to calculate the open days and then using the formula in cell S1 to calculate the average of that row. I wanted to try to have just one formula in cell S1 that does both

I stumped trying to get it to work. I tried this formula {=AVERAGEIF(B5;B400,"B5-TODAY()>0")}. I got a #DIV/0! error

 

[Marcelo Branco]

Maybe this array formula...
=AVERAGE(IF(B5:B400<>"",ABS(B5:B400-TODAY())))
Ctrl+Shift+Enter, not just Enter

M.

 

[Tennisguuy]

Thanks Marcelo that worked. Appreciate it.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.