Weighted average through sumproduct

**as9178** · 01-11-2021, 05:00 AM

Happy New Year all,

I need to find the weighted average of my labour, mats and sub con against their respective values. I used sumproduct about 6 years ago and have forgotten the conventions used.

Any chance of some help? Looking for the answer to be in the amber column on the attached spreadsheet?

Thanks

Andrew

**samba_ravi** · 01-11-2021, 05:20 AM

try below formula in K2, Copy and paste towards down
=SUMPRODUCT(A2:G2,B2:H2,--(A$1:G$1="Cost"))/J2

**bebo021999** · 01-11-2021, 05:46 AM

K2:

=sumproduct(a2:g2*b2:h2)/j2

**as9178** · 01-11-2021, 05:55 AM

Thanks for the responses

If I needed to add another bit on what would second part of the problem be?

There is a new tab called FAC Codes

On the original tab I have inserted a column in column A with some sample FAC codes.

On the FAC code tab I need to have the weighted average against value against each FAC code.

Thanks very much

**samba_ravi** · 01-11-2021, 06:28 AM

try below formula
b2=SUMPRODUCT((Sheet1!$B$2:$H$4)*(Sheet1!$C$2:$I$4)*(Sheet1!$B$1:$H$1="Cost")*(Sheet1!$A$2:$A$4=$A2))/SUMPRODUCT((Sheet1!$B$2:$H$4)*(Sheet1!$B$1:$H$1="Cost")*(Sheet1!$A$2:$A$4=$A2))

**as9178** · 01-11-2021, 09:30 AM

Hi again,

I have tried altering the formula for mats and sub-cons but the output keeps looking wrong.

Could you provide the formula for the cells on the FAC Codes tabs, to calculated the weighted average for labour, materials and sub-cons from the data on the test tab?

**as9178** · 01-12-2021, 09:53 AM

Anyone able to solve the weighted average 3 problem please?

**JeteMc** · 01-14-2021, 11:03 PM

Try pasting the following into cell B2 on the FAC Codes sheet:

Formula:

Please Login or Register  to view this content.

After you have copied the formula by dragging the fill handle down to cell B4 and while B2:B4 are still selected press the Ctrl and c keys
Select cell D2 and press the Ctrl and v keys
Select cell F2 and press the Ctrl and v keys
Press the Esc key
Remove the s from Sub-Cons in cell F1 (or add an s to Sub-Con in cell I1 on the Test sheet).
Let us know if you have any questions.

**as9178** · 01-18-2021, 06:25 AM

Many thanks JeteMc

**JeteMc** · 01-18-2021, 09:38 AM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Weighted average through sumproduct

LinkBack

Thread Tools

Rate This Thread

Display

Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Re: Weighted average through sumproduct

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculating an average vs. weighted average

Conditional Weighted Averages - Take a weighted average of a set of data if it contains sp

Weighted Average Function for Series Weighted by Increments of 1

weighted average with multiple weighted factors using SUMPRODUCT

Query regarding Calculating Weighted average value or average value in Percentage.

[SOLVED] Average Percentage (weighted average) but I want to exclude N/A

[SOLVED] What is this kind of average called?-weighted average

Bookmarks

Bookmarks

Posting Permissions