3 Replies Latest reply on Jun 20, 2016 10:49 AM by Yuriy Fal

    Calculating benefits participation rates by benefit type.

    Jeff Shabani

      I'm trying to create a worksheet that calculates benefits participation rates among people who are eligible for benefits and by specific benefit type. The problem I'm having is that my sheet keeps returning 100% participation, which is not true.

       

      I'm using the following 3 data sources (tabs in the attached Excel file):

      • tblEligible - this is the population of eligible people. There are 2,000.
      • tblParticipants - this is the population of people who are receiving benefits. There are 1,500 unique Employee Numbers but many duplicates and triplicates for those receiving multiple benefits. This table is linked to tblEligible by Employee Number
      • tblBenefits - this is the list of benefits and linked to tblParticipants by Benefit

       

      When I only inlcude the tblEligible and the tblParticipants data sets I get the correct calculation (75%). When I include Benefit I get 100%.

       

      Any help would be appreciated.

       

      Thanks,

      Jeff