Hi @Louis.Smith,
For an average using more than one criteria you need to use AVG & COLLECT:
AVG(COLLECT({Hired Range 1},{Hired Range 3}, {Hired Range 3} > DATE(2023, 3, 1), {Client},"Delta")
I'm not sure which columns your Hired Range 1 & 3 references are referring to, but hopefully this gives you what you're after.
If not, let me know what the issues with it are and we can try and work it out. Hope this helps! ☺️
Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
How to get the average dates for Delta
How to get the average dates for Delta which is located in the client column:
The formula I have is below:
=AVERAGEIF({Hired Range 3}, {Hired Range 3} > DATE(2023, 3, 1), {Hired Range 1})
The sheet is below:
Answers
-
Hi @Louis.Smith,
For an average using more than one criteria you need to use AVG & COLLECT:
AVG(COLLECT({Hired Range 1},{Hired Range 3}, {Hired Range 3} > DATE(2023, 3, 1), {Client},"Delta")
I'm not sure which columns your Hired Range 1 & 3 references are referring to, but hopefully this gives you what you're after.
If not, let me know what the issues with it are and we can try and work it out. Hope this helps! ☺️
-
It gave me #Invalid Ref
Hired Range 3 is referring to the column where the date range is listed.
Hired Range 1 is the amount that we are getting the average of .
-
Have you defined the {Client} reference to tie up with the client column? This can't be typed in, so would need setting up (and renamed if you wished) - all the other references are pre-existing so this is the only thing I can think of that would provide the #Invalid Ref.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!