How to bring back averages based on state criteria

MGreenPKWY

I am trying to figure out the best formula to write to accomplish averages per state code.

At the bottom of the snippet is where I would like for the averages to live.

Example of need:

I'd like for all AZ states to bring back averages of SF, Cost per SF, estimating, General Requirements and existing conditions. What type of formula would I use to make this happen or would I be better off using multiple row reports?

KPH

I would use an AVERAGEIF function:

=AVERAGEIF(State:State, State@row, SF:SF)

This looks at the state column and returns the average of the SF column in all rows where the state matches the state on the current row. You can do the same for all the columns, changing the SF column.

The only issue will be that this formula, if placed in the same column as your data will create a circular reference if used more than one. For example, you could do Arizona, as smartsheet is smart enough to exclude itself. But if you add a row for CA, you will create a circular reference. You either need to put this formula in a different sheet (and use cross sheet references), in different columns (maybe over to the right), or leave it at the bottom but select the rows you want to include, for example:

=AVERAGEIF(State1:State15, State@row, SF1:SF15)

MGreenPKWY

I will try this. Thank you for your help!

KPH

Happy to help!