Calculating weighted average with single criteria

Chanie · May 7th, 2002, 08:16 AM

Rather than calculating an unweighted average if the condition is FALSE, I just need those items not to be included in the weighted average calculation. That is, if a facility is class B (rather than A), I don't want it included in the weighted average for Class A lease rates. How would I be able to make your formula work in this instance?
Also, I discovered that I don't need to do multiple conditions, just one applies. What would you suggest?

The closest I've been able to come (if there is such a thing), is:

=sumproduct(if(g4:g86="a",1,0))*(t4:t86,i4:i86)/sum(i4:i86).

[ This Message was edited by: chanie on 2002-05-07 08:18 ]

[ This Message was edited by: Chanie on 2002-05-07 11:29 ]

Mark W. · May 7th, 2002, 08:32 AM

=IF(AND(condition1,condition2),SUMPRODUCT(B2:B86,C2:C86)/SUM(C2:C86),AVERAGE(B2:B86))

Note: You didn't say what you wanted to calculate if both conditions weren't TRUE so I assumed that you wanted an unweighted average.

[ This Message was edited by: Mark W. on 2002-05-07 07:34 ]

Aladin Akyurek · May 7th, 2002, 10:03 AM

If it's conditional weighted avearge what you want and it looks that way judging from the formula you attempted, I'd think:

=sumproduct((g4:g86="a")*(t4:t86)*(i4:i86))/sumproduct((i4:i86)*(g4:g86="a"))

Mark W. · May 7th, 2002, 01:24 PM

If cells A1:C9 contains...

{"Class","Value","Weight"
;"A",1,4
;"A",2,7
;"B",3,9
;"B",4,8
;"A",5,6
;"B",6,2
;"A",7,1
;"A",8,3}

...the array formula...

{=SUM(IF(A2:A9="A",B2:B9*C2:C9))/SUM(IF(A2:A9="A",C2:C9))}

...returns 3.76190476190476.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 7th, 2002, 08:16 AM	#1
Chanie New Member Join Date: May 2002 Posts: 1	Rather than calculating an unweighted average if the condition is FALSE, I just need those items not to be included in the weighted average calculation. That is, if a facility is class B (rather than A), I don't want it included in the weighted average for Class A lease rates. How would I be able to make your formula work in this instance? Also, I discovered that I don't need to do multiple conditions, just one applies. What would you suggest? The closest I've been able to come (if there is such a thing), is: =sumproduct(if(g4:g86="a",1,0))*(t4:t86,i4:i86)/sum(i4:i86). [ This Message was edited by: chanie on 2002-05-07 08:18 ] [ This Message was edited by: Chanie on 2002-05-07 11:29 ]

May 7th, 2002, 08:32 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(AND(condition1,condition2),SUMPRODUCT(B2:B86,C2:C86)/SUM(C2:C86),AVERAGE(B2:B86)) Note: You didn't say what you wanted to calculate if both conditions weren't TRUE so I assumed that you wanted an unweighted average. [ This Message was edited by: Mark W. on 2002-05-07 07:34 ]

May 7th, 2002, 10:03 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	If it's conditional weighted avearge what you want and it looks that way judging from the formula you attempted, I'd think: =sumproduct((g4:g86="a")(t4:t86)(i4:i86))/sumproduct((i4:i86)*(g4:g86="a"))

May 7th, 2002, 01:24 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	If cells A1:C9 contains... {"Class","Value","Weight" ;"A",1,4 ;"A",2,7 ;"B",3,9 ;"B",4,8 ;"A",5,6 ;"B",6,2 ;"A",7,1 ;"A",8,3} ...the array formula... {=SUM(IF(A2:A9="A",B2:B9*C2:C9))/SUM(IF(A2:A9="A",C2:C9))} ...returns 3.76190476190476. Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.