# Array Question

1. ## 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)

Thanks,

Vitaly

2. ## Re: Array Question

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.

3. ## Re: Array Question

Still working on this problem. Would appreciate any help people can offer.

4. ## Re: Array Question

Maybe =AVERAGE(B1:B26-A1:A26) with Control+Shift+Enter

5. ## Re: Array Question

Doesn't look like it. At least one issue is the blank cells throwing the formula off with a default value of 0.

6. ## Re: Array Question

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).

7. ## Re: Array Question

{=Avergae(B:B-A:A)} gives me a neggative number. Same as making Column C where C1=(B1-A1) etc and then averaging that number. The blank cells are all -429##, thus throwing everything off.

&

9. ## Re: Array Question

Reposted attachment. Couldn't get #6 to work.

10. ## Re: Array Question

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)
If you did it - ignore it.
Thank you.

11. ## Re: Array Question

Or:
``Please Login or Register  to view this content.``

12. ## Re: Array Question

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.`

13. ## Re: Array Question

Awesome. Both solutions above work perfectly. Thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1