Formula for avg labor rates

Sidonia Posteuca

Hello!

I am trying to get an overall average of the amount of labor hours it takes to install equipment per quantity installed.

Here is a screenshot of the following columns I am trying to gather the average from.

I want to get the average labor hours per qty installed per equipment. In this case it would be the average of two with two different quantities. But I would like a formula that I could replicate for other equipment type.

Please help! Thank you!

Mike TV

@Sidonia Posteuca

You're going to want to use an AVERAGEIF and possibly even a SUMIF or SUMIFS. You can see how to set them up here:

https://help.smartsheet.com/function/averageif

https://help.smartsheet.com/function/sumifs

You can even thread the SUMIF(S) into an AVG function by placing the SUMIF(S) into the number1 and number2 positions of the AVG function:

https://help.smartsheet.com/function/avg

Sidonia Posteuca

@Mike TV

Can you give me an example of what the formula would look like please?

Genevieve P.

Hi @Sidonia Posteuca

What I would do in this instance is add a new column to the sheet, a helper column that can identify the number of hours per quantity.

It would be a simple column formula:

=[Feeder Raceways: QTY Installed]@row / [Feeder Raceways: Labor Hours]@row

Since you have quite a few blank rows, I would also add in an IF statement that checks to see if the hours are blank, and if they are, return a blank cell (so you don't get an error):

=IF([Feeder Raceways: Labor Hours]@row = "", "", [Feeder Raceways: QTY Installed]@row / [Feeder Raceways: Labor Hours]@row)

Then you can use AVERAGEIF on this new helper column as Mike mentioned!

=AVERAGEIF([Feeder Raceways: Raceway Type]:[Feeder Raceways: Raceway Type], "Raceway Type to Search", [Helper Column]:[Helper Column])

Does that make sense?

Cheers,

Genevieve