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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!