Calculated cells not calculating correctly

[MATT PENNY]

Good morning all,
I have a query about calculated fields and pivots.

I collect data about customer defects and everything is logged in excel, i then use pivot tables to analyse the data.
so far everything is working ok, but today I wanted to look at averages, so used calculated fields to give me the Sum of avg as shown below.

obviously this is wrong, and I know why it's wrong... the averages are calculated using the sum of QTY in the first line we had 8 complaints, the number of defective pieces was 374, I need average cost per complaint.
I can manually calculate this, but would need to re-do the manual calculation every time I update the sheet (daily)

I'm using excel 2010 if that helps??

Thanks in advance.

 

[jasonb75]

Looks like you've averaged against the number of pieces instead of the number of complaints. Rounded to 2dp, 3812.62/374=10.19

 

[RoryA]

Unless you have Power Pivot, a calculated field will always sum any fields you refer to. You could perhaps add a new column to the source data that just returns 1 for each row so you can sum that as a counter.

 

[MATT PENNY]

Looks like you've averaged against the number of pieces instead of the number of complaints. Rounded to 2dp, 3812.62/374=10.19

Any idea how I can rectify this?
every time I recieve a complaint I enter a new line into an excel table, which updates the pivots?
Thanks

 

[jasonb75]

Any idea how I can rectify this?

Not without seeing the source data, as @RoryA pointed out in post 3, you might need to add another column.