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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,210
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?
 

Forum statistics

Threads
1,176,348
Messages
5,902,635
Members
434,983
Latest member
JenniferNash

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