• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to pull 2012 dates from two arrays and then average their duration

O

onash

New Member
Hi. Newbie to the site.

I need to calculate the cycle times between to dates (milestones)and get the average for those durations for dates occuring in 2012.

So far, I've been subtracting column B from Column A, then using an Average() forumla to average them out.

I'm trying to automate the process by filtering the dates to 2012 in column A, then the same in Column B, subtracting B from A.

I have to do this on multiple milestone sets and then chart them. Finding the right formula would make it much easier.


Thanks.

Omar
 
Hi, onash!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question... consider uploading a sample file; guidelines in second green sticky post.


Regards!
 
Hi, onash!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question... consider uploading a sample file, please. Guidelines in second green sticky post.


Regards!
 
Thanks for the welcome.

I've searched and haven't found exactly what I need.

I've attached a sample workbook. Download here: https://docs.google.com/open?id=0B4rlQn-OsIMGQ0J2dHE5eVRmWmM


What I'm trying to do is automate the look up of 2012 dates without having to create a new column to calculate the duration so I can average them out.
 
Hi, onash!

Using the built-in filter feature of Excel (select columns, Data tab, Sort & Filter group, Filter icon, set AutoFilter on) doesn't work? Then you can apply filter to both columns A & B if needed.

The only issue is the average: do you need the average for filtered data? If so, just add in line 22:

A22: Filtered

B22: Average

C22: =SUBTOTALES(101;C2:C20) -----> in english: =SUBTOTAL(101;C2:C20)

Regards!
 
I've been using the filter manually so far, but I would like to automate it by formulas, as the sample I showed is only one array. The real tracker has 14 sets of dates and is time consuming to add a column after each set then do the duration calculation and average it out.

I was hoping that I could use a sumproduct() formula or something similar.


I can use the following formula to create a separate list on a hidden tab for the dates in 2012 and get the duration, but I can't figure out how to direct the formula to run down the entire column for heading MS1:


'=if(text(a2,"y")=text(E1,"y"),sum(b2-b1),"") where E1= 1/1/2012.


This formula will compute the duration if the year in the first date is 2012. But how can I get it to go to the original tab, find the column header = MS1 and copy the data in that column over to the hidden tab?


Thanks for your help.
 
Hi, Onash,


Try this with CTRL+SHIFT+ENTER, rather than just ENTER.


=AVERAGE(IF(TEXT(B2:B20,"yyyy")="2012",B2:B20-A2:A20))


If you are replacing "2012" with a cell use &"" after cell.


ie E1 = 2012, so use it like


=AVERAGE(IF(TEXT(B2:B20,"yyyy")=E1&"",B2:B20-A2:A20))
 
Haseeb,

I believe that did the trick. I had read about using CTRL+SHIFT+ENTER but completely forgot about it.


I works on the sample file I uploaded. Now I'll try to use it on the full tracker with multiple durations. I'll post my results, but that might have to be later this weekend or Monday.


Thanks for your help.


Omar
 
You must log in or register to reply here.
Back
Top