Report on users who don't estimate well in Excel

I have a spreadsheet corresponding to entries of a user, their estimation, and the actual value (for example: hours for a particular project - again, this is only an example), which we can represent in CSV like:

User,Estimate,Actual
"User 1",5,5
"User 1",7,7
"User 2",3,3
"User 2",9,8
"User 3",6,7
"User 3",8,7
开发者_如何学编程

I'm trying to build a report on these users, to quickly see which users underestimate or overestimate, and so I created a pivot table. But, I can't figure out how to simply show if a user has underestimated at some point. I tried to create a calculated field like =IF(Estimate > Actual, 1, 0), but this sums, then compares the Estimate and Actual columns and tells me that "User 3" doesn't over/underestimate.

Without adding an additional field to my data, how can I accomplish this?

A similar SQL pseudo-query would be:

SELECT DISTINCT al.User,
(SELECT COUNT(*) FROM ActivityLog AS l2 WHERE l2.User = al.User AND l2.Estimate > l2.Actual) AS Overestimates
FROM ActivityLog AS al

Edit:

I'm still working on this, and currently have created a static list of users in some cells on the side, and have given them the Array Formulas: {=SUM(IF((A$2:A20 = F6)*(B$2:B20 > C$2:C20), 1, 0))} and {=SUM(IF((A$2:A20 = F6)*(B$2:B20 < C$2:C20), 1, 0))} (if I have the user's name in F6).

Mainly, I want to do this where the list of users can populate dynamically from the main data.

Calculated fields in pivot tables stink. I would get rid of the pivot table and do it with formulas. Start a unique list of users in H15 and enter this in I15

{=MAX(($A$2:$A$7=H16)*($B$2:$B$7-$C$2:$C$7<>0))}

array entered. This will return 1 if they ever over or under estimated and zero if they never did. The downside is that you can't "refresh" it like a pivot table so you have to make sure your unique user list is accurate all the time.

If that's too big of a downside, I think you'll need to add a column to your source data. Specifically

=ABS(B2-C2)

And add that to your pivot table. It will show zero for never over/under and non-zero otherwise.

You are aware that you should make sure the estimates are all in the same range? Smaller numbers can be estimated better (when talking about hours).

Add a column for actual-estimate

then summarize those values for min max and average. (or stddev)