Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculated Weighted & Conditional Average

1. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
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).

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
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. I agreed to these rules
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

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

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