Announcement

Collapse
No announcement yet.

Calculated Weighted & Conditional Average

Collapse
X
  • 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

    Comment


    • #3
      Re: Calculated Weighted & Conditional Average

      Hi,

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

      In I2 and copied down,

      =A2&B2&E2

      In J2 and copied down,

      =SUMPRODUCT(--($I$2:$I$43=A2&B2&E2),$F$2:$F$43,$G$2:$G$43)/SUMIF($I$2:$I$43,A2&B2&E2,$G$2:$G$43)

      HTH
      Kris

      ExcelFox

      Comment


      • #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!!!

        Comment

        Working...
        X