# Array Question

• 10-13-2017, 10:42 AM
vichisov
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
• 10-13-2017, 10:46 AM
AliGW
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. :)
• 10-16-2017, 04:59 PM
vichisov
Re: Array Question
Still working on this problem. Would appreciate any help people can offer.
• 10-16-2017, 05:09 PM
sandy666
Re: Array Question
Maybe =AVERAGE(B1:B26-A1:A26) with Control+Shift+Enter
• 10-16-2017, 05:13 PM
vichisov
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.
• 10-16-2017, 05:19 PM
sandy666
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)

Quote:

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

• 10-16-2017, 05:32 PM
vichisov
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.
• 10-16-2017, 05:33 PM
sandy666
Re: Array Question
Re-read post#2 point 2
&
• 10-16-2017, 05:47 PM
vichisov
Re: Array Question
Reposted attachment. Couldn't get #6 to work.
• 10-16-2017, 06:46 PM
sandy666
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)
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. :cool:
• 10-16-2017, 07:17 PM
leelnich
Re: Array Question
Or:
Code:

`=(SUM(B1:B26)-SUMIF(B1:B26,">0",A1:A26))/COUNT(B1:B26)`
• 10-16-2017, 08:09 PM
AlKey
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:
`=AVERAGE(IF((A1:A26<>"")*(B1:B26<>""),B1:B26-A1:A26))`
• 10-17-2017, 09:44 AM
vichisov
Re: Array Question
Awesome. Both solutions above work perfectly. Thank you!