Report on users who don't estimate well in Excel

Question

I have a spreadsheet corresponding to entries of a user, their estimation, and the actual value (say, of hours for a particular project), 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

Answer 1

add a column for actual-estimate

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

Answer 2

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.

Answer 3

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).