Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Calculated Weighted & Conditional Average

  1. #1
    Join Date
    22nd January 2009
    Posts
    2

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,831

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  4. #4
    Join Date
    22nd January 2009
    Posts
    2

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Conditional Weighted Average Within Time Span
    By nag451 in forum Excel General
    Replies: 2
    Last Post: February 24th, 2008, 04:03
  2. Conditional Weighted Average?
    By Rweasel6 in forum Excel General
    Replies: 1
    Last Post: April 11th, 2006, 13:24
  3. Weighted Average...
    By jemagnussen in forum Excel General
    Replies: 15
    Last Post: February 16th, 2006, 07:19
  4. Weighted Average
    By Kim1978 in forum Excel General
    Replies: 2
    Last Post: April 8th, 2005, 05:52
  5. Weighted Average
    By Pranob in forum Excel General
    Replies: 2
    Last Post: October 28th, 2004, 14:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno