Calculating average based on required criteria

S

SColyn

New Member
Joined
May 12, 2016
Messages
19
Good day,

I require assistance please.

I have to calculate an average based on the monthly contract data.

We have a Hit rate per customer per month and some of our customer have contracts with us, but the contracts were not implemented the beginning of the year. Some were only implemented August / September.

I have to show the Hit rate average for contract customer and non contract customers but I have to show the months the customer did not have a contract under non contract and visa versa.

I am a blank with how to calculate this and would appreciate guidance.

Below is the Pivot table (and I use to filter on Contracts "Yes / No", but have to include accurate results and cannot do it this way anymore)
[TABLE="width: 1005"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Hit Rate[/TD]
[TD][/TD]
[TD]Months[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Market Company[/TD]
[TD]Customer Plant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CUSTOMER 10 SOUTHERN AFRICA BOKSBURG[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CUSTOMER 7 LUSAKA[/TD]
[TD]*[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD]South Africa Total[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]57%[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"]77%[/TD]
[/TR]
</tbody><colgroup><col><col><col span="8"></colgroup>[/TABLE]


Below is the data (but the last column "Contract" is a vlookup from the Contract data sheet and only shows the current status)
[TABLE="width: 1355"]
<tbody>[TR]
[TD]Country and Business Area[/TD]
[TD]Customer Plant[/TD]
[TD]Line Code[/TD]
[TD]Asset Code[/TD]
[TD]Serial Number[/TD]
[TD]Total Potential Events[/TD]
[TD]TP Completed Events (Hits)[/TD]
[TD]Date[/TD]
[TD]Contract[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GAB-LN Y[/TD]
[TD="align: right"]9000030871[/TD]
[TD]20134/81339[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN G[/TD]
[TD="align: right"]9000050716[/TD]
[TD]13372/11230[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN S[/TD]
[TD="align: right"]9000049891[/TD]
[TD]20179/81785[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN W[/TD]
[TD="align: right"]9000030865[/TD]
[TD]20134/81333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN G[/TD]
[TD="align: right"]9000050716[/TD]
[TD]13372/11230[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]May-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN S[/TD]
[TD="align: right"]9000049891[/TD]
[TD]20179/81785[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]May-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN W[/TD]
[TD="align: right"]9000030865[/TD]
[TD]20134/81333[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]May-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN Y[/TD]
[TD="align: right"]9000030871[/TD]
[TD]20134/81339[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]May-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN G[/TD]
[TD="align: right"]9000050716[/TD]
[TD]13372/11230[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Aug-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN S[/TD]
[TD="align: right"]9000049891[/TD]
[TD]20179/81785[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Aug-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN W[/TD]
[TD="align: right"]9000030865[/TD]
[TD]20134/81333[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Aug-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN Y[/TD]
[TD="align: right"]9000030871[/TD]
[TD]20134/81339[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Aug-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN G[/TD]
[TD="align: right"]9000050716[/TD]
[TD]13372/11230[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Sep-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN S[/TD]
[TD="align: right"]9000049891[/TD]
[TD]20179/81785[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Sep-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN W[/TD]
[TD="align: right"]9000030865[/TD]
[TD]20134/81333[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Sep-17[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Botswana Carton[/TD]
[TD]CUSTOMER 1 BOTSWANA[/TD]
[TD]BWC-CUSTOMER 1 GBR-LN Y[/TD]
[TD="align: right"]9000030871[/TD]
[TD]20134/81339[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Sep-17[/TD]
[TD]Yes[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

Below is my Contract data sheet
[TABLE="width: 929"]
<tbody>[TR]
[TD]Market Company[/TD]
[TD]Asset Code[/TD]
[TD]Contract[/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]Dec-17[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000049829[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000049838[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000052726[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000049835[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000079618[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000079619[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000085414[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000079618[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000079619[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000085414[/TD]
[TD]No[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000030871[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000049891[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000050716[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000030871[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000049891[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]9000030865[/TD]
[TD]Yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You must log in or register to reply here.

Similar threads

J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
866
Cubist
Cubist
N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
590
NaushadAnwar
N
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
542
glaccounting
G
M
  • Solved
Calculate the headcount at the end of each month based on the start/leave dates, split by employee type and country.
Replies
5
Views
897
Fluff
Fluff
D
  • Question Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
678
Herakles
H

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top