# Formula for avg labor rates

Options

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!

## Answers

• ✭✭✭✭✭✭
Options

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:

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:

• Options

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

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!