3 Replies Latest reply on Apr 20, 2018 12:27 PM by Pradeep Repala

    Grand Totals and horizontal vs. vertical calculations (subtotals should account for the weight of the number in each cell)

    luc dumont

      Hello -

       

      Hoping someone can help me with this. I have a problem with the "Actual Completes (%)" (3rd column), the subtotal is a simple average of cells for that category, I want it to be a weighted average.

       

       

      ****PLEASE DON'T CONSIDER THE NULL LINE******

      For category 1, the subtotal of "Actual Completes (%)" computed as a simple average like the following: (82% + 55% + 63%) / 3 = 66 % ( I know it should be 67 but the numbers in the cell are round up), and it is not what I want....

       

      For you to better understand, "Actual Completes (%)" = "Actual Completes (#)" / "Quota"  ---- (or 3rd column = 2nd column / 1st column)

       

      My problem is that each cell has its own "dynamic" weight, but subtotals do not take it into account.

       

      For example, for "category 1" and "Quebec"

      for subcategory A 82% represent 129 completes / 158  ---- (then its weight when computing the average should be 158 of 271 (twich is the total of "Quota" for tha category)

      for subcategory B 55% represent 49 completes / 89  ---- (then its weight when computing the average should be 89 of 271 wich is the total of "Quota" for tha category)

      for subcategory C 63% represent 15 completes / 24  ---- (then its weight when computing the average should be 24 of 271 (wich is the total of "Quota" for tha category)

       

      So what I would like to see in the total for "Actual Completes (%)" would be 82%*(158/271) + 55%*(89/271) + 63%*(24/271) = 71%

       

      Picture1.png

       

      I tried to use Table Calculation (Table (across), Table (down), etc.) and could not figure it out. The subtotal is always a simple average of whatever is already in the column...

       

      Need help