Hello. I am trying to formulate the following problem.
I am looking to calculate the average Number of Resources (col D), for each DISTINCT Deal ID (col A) where the start date < Jan-10 and the end date > Jan-10.
Any suggestions?
The answer in this case should be 2.5. Because Deal #1 is a duplicate, I only want to include 1 row in the average. Thank you!
Not sure how you arrive at 2.5 as the result. Dropping one of the 4's results in 7 for the total # of resources, and divided by 3 gives the result of 2.3333
I used this formula to arrive at the total number of unique ID's
=SUM(IF(FREQUENCY(A2:A5,A2:A5)>0,1))
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
WE could do this by VBA but in this method, I just used a helper column F.
F2: =IF(AND(A2<>A1,AND(DATE(2013,1,10)>=B2,DATE(2013,1,10)<=C2)),E2,"")
Drag fill or copy F2 down to F5.
F6: =AVERAGE(F2:F5)
thanks. Aussiebear, Deal #3 does not meet the selection criteria. So the 2.5 = the average of 4 and 1.
All too true. Forgot about the between dates criteria.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
