Avg # of Calls per Sales Person a Year??

[kimallen]

Hi all, I'm trying to figure out how to do an average number of sales calls per sales person per year. The problem I'm having is there is only call data in the table I'll be using with no numeric values to base the average on (only the date of the call).

Obviously I can't do an average on a text field so don't know where to begin. Does anyone know of a way you can get around this?

Thanks

 

[BrianMH]

Sheet1

*	A	B	C	D
Agent	Call Date	*	Average Calls
Brian	*
Mark	*	*
John	*	*
Brian	*	*
Mark	*	*
John	*	*
Brian	*	*
Mark	*	*
John	*	*
Brian	*	*
Mark	*	*
John	*	*
Brian	*	*
Mark	*	*
John	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:75px;"><col style="width:64px;"><col style="width:163px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]14/11/2013[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]30/05/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]14/05/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]11/10/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]02/01/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]13/11/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]23/05/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]06/08/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]13/04/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]06/12/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]26/11/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]24/04/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]21/03/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]27/02/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]23/07/2013[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 {=COUNTA(B2:B16)/SUM(1/COUNTIF(A2:A16,A2:A16))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[MD610]

To do this with a PowerPivot Measure, you would use something like this:

AvgCalls: = AVERAGEX(TableName, COUNTROWS(TableName))

This Measure will give you the average calls.

By creating a Pivot and adding sales person name to Rows, the Year to Columns, and the new Measure, AvgCalls, to Values, you will see Avg Calls Per Person Per Year.

 

[kimallen]

Thank you Brian and Mike. Mike I will try the COUNTROWS tomorrow. I'm guessing I can do it directly in the table instead of a straight pivot table measure?

 

[MD610]

This really won't be your final solution. I was trying to give you a general pattern to follow to get numeric averages from text counts. The actual solution will be dependent on what your data actually looks like.

In general, sometimes a measure can be interchangeable with a calculated column, but usually they need to be modified a bit to generate the same results (if the same results are possible). This can also be dependent on what you data looks like.

If you provide some sample data, I can probably help you come up with a better, more specific solution.