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

dyanna

New Member
Joined
Jan 4, 2018
Messages
14
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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dyanna

New Member
Joined
Jan 4, 2018
Messages
14


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.





B
C
D
E
F
G
1
Comp #
Dist #
Loc #
Trans Error Type 1
Trans Error Type 2
Population Lookup
2
121
12122
12345
1
1
60
3
121
12123
23456
0
1
65
4
424
42411
34567
1
0
45
5
424
42411
34567
0
1
45
6
424
42433
45678
1
0
18
7
828
82899
56789
1
0
90
8
828
82899
56789
0
0
90
9
828
82899
56789
0
1
90
10
828
82899
56789
0
1
90
<tbody> </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.





B
C
D
E
1
Comp #
Dist #
Loc #
Trans Count
2
121
12122
12345
60
3
121
12123
23456
65
4
424
42411
34567
45
5
424
42411
34567
45
6
424
42433
45678
18
7
828
82899
56789
90
8
828
82899
56789
90
9
828
82899
56789
90
10
828
82899
56789
90
<tbody> </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

New Member
Joined
Jan 4, 2018
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top