Having trouble showing min, max, average on grouped data in a Pivot Table

Hello!

I feel like I'm overlooking something obvious, but here's my question.

I have data as follows:

Earned____Date__________________Employee
140_______5/16/2011 5:16:16 PM____Bob
100_______5/17/2011 11:11:12 AM___Bob
230_______5/17/2011 11:16:46 AM___Jane
100_______5/17/2011 11:30:00 AM___Bob
200_______6/23/2011 1:16:16 PM____Jane
150_______6/23/2011 1:30:16 PM____Bob
310_______6/24/2011 2:10:33 PM____Bob
110_______6/25/2011 4:46:56 PM____Jane


I want to create a pivot table that shows me, say, the largest single earned value for, well, let's group by 7 day period:
_______________________Bob___Jane
5/15/2011 - 5/21/2011_____140____230
5/22/2011 - 5/28/2011_____310____200

I can format my pivot table exactly how I want it by grouping the date by 7 days, putting Employee in the Legend fields, Date in the Axis fields and Earned in the Values. It looks like it's going to work if I set my Value field settings to "Count of Earned", but if I try "max", "min", etc. I get "0", and "average" gets me a lovely #DIV/0!

I feel like I'm missing something obvious.

As my original data is coming from a database query and will grow over time I don't think adding new columns will work.

Any suggestions?

(sorry about the underscores, I didn't see any other way to format my table nicely)

Hi SG2,
The normal reason for this type of behavior is that you have some text in your earned column. It may be blanks or letters. Count works fine but Pivot Tables are very picky about needing all numbers to do the math type answers.

Insure you have all numbers in the field you are trying to sum.

hope that helps.