ExcelBanter - Create Formula for Weighted Average in non-consecutive cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Create Formula for Weighted Average in non-consecutive cells (https://www.excelbanter.com/excel-worksheet-functions/265292-create-formula-weighted-average-non-consecutive-cells.html)

Create Formula for Weighted Average in non-consecutive cells

I have a spreadsheet which across the top has the months Jan to Dec and
Total. Under each month is 4 categories Days/oil/gas and would like to
simplify a formula for a weighted avg. eg.

A B C D E F G H I
JANUARY FEBRUARY MARCH etc. to December
days Oil gas days Oil Gas days Oil Gas
6 22 13 4 5 18 7 12 15

formula: =(a1*b1+d1*e1+g1*h1)/(a1+e1+g1)

Is there any way to simplity this. Thanks for any help anyone can give.
ferne

Quote:

Originally Posted by ferne (Post 958532)

If your table is setup as such:

A B C D E F G H I
1 Days Oil Gas Days Oil Gas Days Oil Gas
2 6 22 13 4 5 18 7 12 15

If your formula is =[(Days*Oil)/Gas]

=(SUMIF(A1:I1,"Days",A2:I2)*(SUMIF(A1:I1,"Oil",A2, I2))/SUMIF(A1:I1,"Gas",A2,I2)

But if the table is set such as:

A B C D E F
1 Jan Feb Mar Apr May
2 Days 2 5 3 9 21
3 Oil 61 61 45 12 65
4 Gas 9 65 132 65 16

=(SUM(B2:F2))*(SUM(B3:F3))/SUM(B9:F9)