How to bring back averages based on state criteria
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?
Answers
-
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)
-
I will try this. Thank you for your help!
-
Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!