Need to incorporate popultation report into pivot table to calculate error rate

[dyanna]

I have an Excel 2010 workbook that includes the detail for a portion of the population of data. The portion was selected based on certain criteria. Research was completed to test compliance. The results are displayed in a pivot table.
I need to calculate error rate based on a second report that contains population count at the company, district, and location level.
I've tried adding the population to a new column in the detail as a lookup then adding this column to the pivot table with a Max value which may work for one monthly period but will not work for future monthlt/Qtrly/YTD purposes. advice/suggestion is welcome!

 

[dyanna]

Basedon the lack of response to my post I suspect additional detail is needed toexplain the question.


Ihave two reports.
Thefirst report contains a sample, not the full list of transactions processed,with detail that includes Company, District and Location identifiers and the resultsof the research performed on the samples.

Researchwas completed to test compliance to more than one rule for each transaction.

<tbody> [TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"]

B​

[/TD]
[TD="width: 80, bgcolor: transparent"]

C​

[/TD]
[TD="width: 80, bgcolor: transparent"]

D​

[/TD]
[TD="width: 153, bgcolor: transparent"]

E​

[/TD]
[TD="width: 153, bgcolor: transparent"]

F​

[/TD]
[TD="width: 170, bgcolor: transparent"]

G​

[/TD]
[TD="width: 80, bgcolor: transparent"] 1 [/TD]
[TD="width: 80, bgcolor: transparent"]

Comp #​

[/TD]
[TD="width: 80, bgcolor: transparent"]

Dist #​

[/TD]
[TD="width: 80, bgcolor: transparent"]

Loc #​

[/TD]
[TD="width: 153, bgcolor: transparent"]

Trans Error Type 1​

[/TD]
[TD="width: 153, bgcolor: transparent"]

Trans Error Type 2​

[/TD]
[TD="width: 170, bgcolor: transparent"]

Population Lookup​

[/TD]
[TD="width: 80, bgcolor: transparent"] 2 [/TD]
[TD="width: 80, bgcolor: transparent"]

121​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12122​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12345​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 170, bgcolor: transparent"]

60​

[/TD]
[TD="width: 80, bgcolor: transparent"] 3 [/TD]
[TD="width: 80, bgcolor: transparent"]

121​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12123​

[/TD]
[TD="width: 80, bgcolor: transparent"]

23456​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 170, bgcolor: transparent"]

65​

[/TD]
[TD="width: 80, bgcolor: transparent"] 4 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42411​

[/TD]
[TD="width: 80, bgcolor: transparent"]

34567​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 170, bgcolor: transparent"]

45​

[/TD]
[TD="width: 80, bgcolor: transparent"] 5 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42411​

[/TD]
[TD="width: 80, bgcolor: transparent"]

34567​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 170, bgcolor: transparent"]

45​

[/TD]
[TD="width: 80, bgcolor: transparent"] 6 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42433​

[/TD]
[TD="width: 80, bgcolor: transparent"]

45678​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 170, bgcolor: transparent"]

18​

[/TD]
[TD="width: 80, bgcolor: transparent"] 7 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 170, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 8 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 170, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 9 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 170, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 10 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

0​

[/TD]
[TD="width: 153, bgcolor: transparent"]

1​

[/TD]
[TD="width: 170, bgcolor: transparent"]

90​

[/TD]
</tbody>

Inorder to calculate error rate, I have been provided with a population reportthat contains the count of transactions for each Location as well as the relatedDistrict and Company.

<tbody> [TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"]

B​

[/TD]
[TD="width: 80, bgcolor: transparent"]

C​

[/TD]
[TD="width: 80, bgcolor: transparent"]

D​

[/TD]
[TD="width: 153, bgcolor: transparent"]

E​

[/TD]
[TD="width: 80, bgcolor: transparent"] 1 [/TD]
[TD="width: 80, bgcolor: transparent"]

Comp #​

[/TD]
[TD="width: 80, bgcolor: transparent"]

Dist #​

[/TD]
[TD="width: 80, bgcolor: transparent"]

Loc #​

[/TD]
[TD="width: 153, bgcolor: transparent"]

Trans Count​

[/TD]
[TD="width: 80, bgcolor: transparent"] 2 [/TD]
[TD="width: 80, bgcolor: transparent"]

121​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12122​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12345​

[/TD]
[TD="width: 153, bgcolor: transparent"]

60​

[/TD]
[TD="width: 80, bgcolor: transparent"] 3 [/TD]
[TD="width: 80, bgcolor: transparent"]

121​

[/TD]
[TD="width: 80, bgcolor: transparent"]

12123​

[/TD]
[TD="width: 80, bgcolor: transparent"]

23456​

[/TD]
[TD="width: 153, bgcolor: transparent"]

65​

[/TD]
[TD="width: 80, bgcolor: transparent"] 4 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42411​

[/TD]
[TD="width: 80, bgcolor: transparent"]

34567​

[/TD]
[TD="width: 153, bgcolor: transparent"]

45​

[/TD]
[TD="width: 80, bgcolor: transparent"] 5 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42411​

[/TD]
[TD="width: 80, bgcolor: transparent"]

34567​

[/TD]
[TD="width: 153, bgcolor: transparent"]

45​

[/TD]
[TD="width: 80, bgcolor: transparent"] 6 [/TD]
[TD="width: 80, bgcolor: transparent"]

424​

[/TD]
[TD="width: 80, bgcolor: transparent"]

42433​

[/TD]
[TD="width: 80, bgcolor: transparent"]

45678​

[/TD]
[TD="width: 153, bgcolor: transparent"]

18​

[/TD]
[TD="width: 80, bgcolor: transparent"] 7 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 8 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 9 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

90​

[/TD]
[TD="width: 80, bgcolor: transparent"] 10 [/TD]
[TD="width: 80, bgcolor: transparent"]

828​

[/TD]
[TD="width: 80, bgcolor: transparent"]

82899​

[/TD]
[TD="width: 80, bgcolor: transparent"]

56789​

[/TD]
[TD="width: 153, bgcolor: transparent"]

90​

[/TD]
</tbody>

Ineed to be able to calculate error rate at the Company, District and Locationlevel based on the population/# errors.




Ihave a pivot table set up to summarize results at the Company, District andLocation level but cannot figure out how to incorporate the data from thepopulation report to add Calculated Fields in the pivot table to calculateerror rate.

All suggestionsare welcome and appreciated!

 

[dyanna]

For the record, I am appalled at the formatting issues in my post. In an effort to avoid typos created as a result of the Auto-Update feature in this thread, I typed my comments in Word and pasted into the post to no avail.