No announcement yet.

Calculated Weighted & Conditional Average

  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculated Weighted & Conditional Average

    The attachment is only a small portion of the data I need to analyse, but is representative of the full set of data.

    What I am trying to do is calculate a weighted average of each of the chemicals shown by location and machine usage and also still be able to use the filter or sort funtions. I have created a sample calculation in cell F9 to further explain this calculation.

    I would like to be able to use the A-Z sorting icon in any of the columns, while still computing the weighted average of the respected chemicals. As it is now, the referenced cells just remain as the 6-7 cells above the calculation instead of the 6-7 cells that apply to the specific chemical at the location and machine (in this example - caustic at location1 on mch-03). For example, if I try to sort in column F, I'd still like to see my example calculation to reference the same values as it is now, calculating the same resulting 60.719.
    Attached Files

  • #2
    Re: Lock Cell Reference By Row Or Catagory When Sorting

    A couple of approaches illustrated in attachment - ditch the total rows and add a formula (col H, which I know somebody here will be able to do more efficiently) or use a pivot table (needs extra col G).
    Attached Files


    • #3
      Re: Calculated Weighted & Conditional Average


      Using Stephen's layout, insert a Col before Col J,

      In I2 and copied down,


      In J2 and copied down,





      • #4

        Re: Calculated Weighted & Conditional Average

        Wow, thanks to you both! This is amazing and super helpful. And I can even add tons more rows without having to change much in the formula or hand work everything. I really appreciate your help!!!