Weighted average with embedded IF function

mcphea02

New Member
Joined
Jul 30, 2010
Messages
2
Hi,

I'm looking for a formula to determine a weighted average of productivity rates (tonnes/hour), weighted by their hours but only if the denominator is nonzero.

I have the tonnes in the vector A1:A10 and the hours in the vector B1:B10.

Thanks!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Presumably the hours won't be zero when the tonnes has a value? In which case you can just divide total tonnes by total hours, i.e.

=SUM(A1:A10)/SUM(B1:B10)/24

Format as number

The above assumes that your "hours" are in time format, e.g. 30 hours is displayed as 30:00, rather than just 30. If display is as per the latter then remove /24 from formula
 

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
Calculate the tonnes/hour with

=IF(B1>0,A1/B1,0) placed in C1 and Fill down to C10. Calculate the average with

=AVERAGE(C1:C10) in C11.
 

mcphea02

New Member
Joined
Jul 30, 2010
Messages
2
Hi guys,

Thanks for the responses but I still haven't found what I'm looking for.

Barry; I agree, presumably the hours won't be zero when tonnes has a value however I need it to include an IF function so that the average isn't diluted with a bunch of zero productivity rates.

Miles; That would create a simple arithmetic average of the productivity rates, I'm looking for an average weighted by the hours. Also, I'm hoping to have a single formula which performs this function.

Hope you guys can help,

Regards.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi guys,

Thanks for the responses but I still haven't found what I'm looking for.

Barry; I agree, presumably the hours won't be zero when tonnes has a value however I need it to include an IF function so that the average isn't diluted with a bunch of zero productivity rates.

Miles; That would create a simple arithmetic average of the productivity rates, I'm looking for an average weighted by the hours. Also, I'm hoping to have a single formula which performs this function.

Hope you guys can help,

Regards.

Care to provide a tiny sample along with the desired result?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,820
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top