1 Reply Latest reply on Oct 27, 2016 3:56 PM by Patrick A Van Der Hyde

    Weighted average & growth rate

    Ekaterina Ponkratova

      Hi All,

       

      Usually before asking for help, I google an topic extensively and I am able to find an answer, but today I got so desperate trying to find a solution to my problem, that I am posting it here.

       

      Here is the challenge: I have data with the insurance claims for 2013 and 2014 for three hospitals located in two states with every hospital having a different set of medical procedures (CPT-4) that were covered. For example, the hospital 1 (H1) which is located in State 1 and belong to type 1 covered the following procedures: 44970, 90862, 49520, 23615, etc. for population of a certain age (Age bucket).

       

      The end result should be: (1) weights.

      (2) a weighted (as a weight, we take a count of Folder number) growth rate per a hospital. However, weights should change according to the selected filters: State, hospital type, specialty (a bundle of CPT-4, e.g. cardiology includes 44970 and 90862), age bucket, and insurer.

       

      I assume I should use LoD but because I never used it, I am a bit confused.

       

      I will be very grateful if you could help somehow...

       

      Update: I am attaching the workbook, so that you could see my calculation steps. I want to reframe my problem and calculation necessary:

      1. Calculate a weighted amount per a hospital which is made of weighted amounts per a medical procedure for a particular hospital. In the Excel world, we would use SUMPRODUCT to calculate it.

      To give you an idea, here is the screenshot of sheet 10 from the attached workbook where the col Calculation is basically a sum of the if_null column. What I need is the total row which will be equal to 57,180 for 2013 and 120,153 for 2014.

      2. Calculate a growth rate of the summed weighted amount per a hospital. In the above case, it should be equal to approx 110%.

       

      Any idea?