Array Question

This is embarrassing to ask, but I can't get a formula to work. I have Column A w/ a set of dates. Column B is another set of dates, all greater than their counterparts in Column A. Some dates in B are blank (process not finished). How do I get an average time of completion e.g. average between B-A)

Added attachment.

Thanks,

Vitaly

And what exactly is the formula??? How are we supposed to help you to troubleshoot it without knowing what you have tried?

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

What about ColA if ColB is blank? ( 0 )

formula above is single result formula not for dragging down

you can try:
=AVERAGEIF(B1:B26,"<>""",B1:B26)-AVERAGE(A1:A26)

but with your description I don't know what you want to achieve.
Your example file should contain BEFORE and AFTER (result)

Originally Posted by AliGW

2. Make sure that your desired solution is also shown (mock up the results manually).

Re-read post#2 point 2
&
re-read post #6

try this one:

=SUMPRODUCT((B1:B26<>"")*(B1:B26-A1:A26))/COUNT(B1:B26)

and probably you will need change cell format to general or number.


If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.

Or:

Please Login or Register  to view this content.

It is quite possible that blanks can be in both, A and B columns. Therefor I would suggest an array formula

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula:

Please Login or Register  to view this content.