Cant find how to get average dates difference between averaged cells, w/out extra cells

I'd like a report using only one cell that will take the average of Column B - the average of column A to see what the average turn around time is.

The formula I have is below but it's not working...
=SUM(AVERAGE(B:B)/COUNTIFS(B:B,">0",A:A, ">0")-(AVERAGE(A:A)/COUNTIFS(A:A,">0",B:B,">0")))

I tried the below formula and it didn't work either...
=SUM(AVERAGE(B:B)/COUNTIF(B:B,">0")-(AVERAGE(A:A)/COUNTIF(A:A,">0")))

I found if I use 3 cells I can make it work by doing AVERAGE(B:B) in one cell, AVERAGE(A:A) in another then in the third cell just doing =SUM() of the two cells.

How can I make this work?

Hi Jeremy,

Welcome to the forum!

You have a lot of data where you either don't have a Scrubbed Date or there is a Completed Date missing.

How do you want to deal with this?

I would be inclined to have a "helper column" with the formula =IF(AND(A2>0,B2>0),B2-A2,"") - copied down, then use the =AVERAGE(C:C)

I hope this helps, please let me know!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David A Coop,

Thank you for your help!

I've stripped this down since there's a lot of personal info I cant share that would go on the sheet. The blanks will need to stay on the sheet and I can't have any helper columns so I'm looking for a way to weed out the blanks and calculate the average without another column.

Hi Jeremy,

I think you will find that Marvin has proposed an elegant solution! His formula was -

=AVERAGE((B2:B1105<>"")*(A2:A1105<>"")*(B2:B1105)-(B2:B1105<>"")*(A2:A1105<>"")*(A2:A1105)) Don't forget, this is an ARRAY formula to be entered using Ctrl+Shift+Enter.

This works well, and there is no need for a helper column, Marvin only included it to show both possibilities.

That said, please don't be afraid of helper columns! They don't even have to be on the same sheet! However, they often make calculations much easier to understand. Marvin's formula works beautifully, but it is quite complex. An inexperienced Excel user would be totally confused!

I hope this helps, please let me know!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi Ford,

That was my first thought, but the problem is with the data! Sometimes Col A has a date, but Col B is blank - and vice versa, so you get a different answer.

Regards,

David