Clarification Using AVG() with COLLECT()
I am looking to understand how the COLLECT() function nested within the AVG() function works and is evaluated. Using the help files, I found the following example. I am specifically looking at the very last formula where the result is "60". I understand this example completely.
However, when I look at the example shown on the Smartsheet Formula Example sheet it doesn't appear to work the same. The formula is the following:
=AVG(COLLECT([Value 2]153:[Value 2]156, [Value 2]153:[Value 2]156, >10))
If I understand correctly, the COLLECT() function is "collecting" the values in the column identified as "Value 2" (which in this case contains the values $12, $17, $10 & $10) within the range specificed (rows 153 to 156) and returning those two values to the AVG() function for calculation. I'm assuming the only two values that would be returned and ultimately averaged together would be the $12 and $17. Therefore, the result of AVG() of those two numbers would be ((12+17)/2) = $14.50. This would be the same method of calculating the average as shown in the HELP example. Is this correct?
If the amount of $14.50 is correct, why does the answer highlighted in green say $13.00? What am I missing?
Any insight you could provide would be appreciated.
Thank you.
Best Answers

You are absolutely correct in outlining how the COLLECT function works!
The reason you see a different number in this Formula Test sheet is that the cells it's highlighting ([Value 2]153:[Value 2]156) actually have decimals that are hidden.
You can check this by doubleclicking any one of the cells to see the full Value. To adjust this, select those 4 cells and use the decimal feature in the formatting bar at the top of your sheet to show up to 2 decimal places. This will reveal the actual numbers:
$12.25
$16.50
$9.75
$10.25
Therefore, the $10.25 is greater than 10, so the COLLECT function will bring these three numbers to be averaged: $12.25 / $16.50 / $10.25. This is 13.
Hope that helps!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
No problem!
There's no indication or flag in the cell itself... but formatting like this would be something you would manually create/set on your column if you're building your own sheet (versus downloading a template with preset formatting like this). Our Help Center article on formatting has more information (see here).
Additionally, in the Text/Number column, text will appear leftaligned whereas numerical data will appear rightaligned in the cell.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

You are absolutely correct in outlining how the COLLECT function works!
The reason you see a different number in this Formula Test sheet is that the cells it's highlighting ([Value 2]153:[Value 2]156) actually have decimals that are hidden.
You can check this by doubleclicking any one of the cells to see the full Value. To adjust this, select those 4 cells and use the decimal feature in the formatting bar at the top of your sheet to show up to 2 decimal places. This will reveal the actual numbers:
$12.25
$16.50
$9.75
$10.25
Therefore, the $10.25 is greater than 10, so the COLLECT function will bring these three numbers to be averaged: $12.25 / $16.50 / $10.25. This is 13.
Hope that helps!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Ahh...I see. Thanks for the info.
So in the future, how would I know whether or not the value in a cell includes decimals without doubleclicking a cell in "x" number of columns to find out? Is there any type of indication on the sheet that flags the fact decimals are hidden? For example, in Excel, if a formula or fomatting doesn't appear to match the rest of the column, or if a number is saved as "text", etc., an indication appears in one corner of the cell. Is there a feature like this or similar in Smartsheet?

No problem!
There's no indication or flag in the cell itself... but formatting like this would be something you would manually create/set on your column if you're building your own sheet (versus downloading a template with preset formatting like this). Our Help Center article on formatting has more information (see here).
Additionally, in the Text/Number column, text will appear leftaligned whereas numerical data will appear rightaligned in the cell.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!