PDA

View Full Version : Formulate average count of DISTINCT records

fb7894
07-25-2013, 03:34 PM
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!

Aussiebear
07-25-2013, 11:21 PM
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))

Kenneth Hobs
07-25-2013, 11:49 PM
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)

fb7894
07-26-2013, 12:34 AM
thanks. Aussiebear, Deal #3 does not meet the selection criteria. So the 2.5 = the average of 4 and 1.

Aussiebear
07-26-2013, 03:41 AM
All too true. Forgot about the between dates criteria.