My SUM formula yields a maximum of 56, but I want to cap it at 40.
I have a form where various answers to questions will give the applicant 2 points. If the applicant answers all questions, they would get 56, however, we are capping it at 40. How can I stop the counting after 40?
Thank you!
Best Answer
-
Many thanks for your response! I actually found a way to do this in addition to the way you cited. Here's what worked on my end:
=MIN(40, (SUM([Formula - Honor Society 2018-2019]@row:[Formula Volunteer Activities 21-22]@row)))
Thanks again!
Answers
-
Where should the 40 be displayed? On the form for the user? Or on the sheet for whomever is reviewing the form submissions?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Good question - the formula now yields 56 on the sheet and only needs to be displayed on the sheet. So, if the responses the applicant only calculate to 38, I want the formula to display 38 - but, if they calculate to 44 for example, I only want the formula to display 40. i.e., anything in excess of 40 would not be shown by the formula.
-
Hoping there is a "not to exceed" function?
-
I think the best way to do this is with a helper column to evaluate the result of the formula and give you results in that helper column. Call the column something like "AnswerTotal" and use an IF statement. here I'm using "ApplicantAnswers" as the column where your current SUM formula is. In your new "AnswersTotal" column use this formula:
=IF(ApplicantAnswers@row <= 40, ApplicantAnswers@row, 40)
In English, if the sum value in ApplicantAnswers is 40 or less, use the value from ApplicantAnswers in this cell, otherwise, just put 40 in this cell.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Many thanks for your response! I actually found a way to do this in addition to the way you cited. Here's what worked on my end:
=MIN(40, (SUM([Formula - Honor Society 2018-2019]@row:[Formula Volunteer Activities 21-22]@row)))
Thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!