Average Across ONLY IF NOT Blank Going Across Grid.
Hi,
How can i keep a cell at "Incomplete" until all 3 scores are entered going across.
=IFERROR(AVG([Risk Impact- Monetary]@row:[Risk Impact-Operational]@row), "Incomplete")
Best Answer
-
I'm sorry, didn't you ask about checking just 3 cells? 🤣
Ok, here's a different approach:
=IF(COUNTIFS([Governance Structure]@row:[Contracting/Vendors]@row), ISBLANK(@cell)) > 0, "Not Done", "Done")
COUNTIFS will look across the row range and count any blank cells. IF the count is more than 0, it's Not Done, if the count is 0, it's Done.
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!
Answers
-
You were on the right track.
IF(logical expression, true condition, false condition)
AND function will evaluate if all the conditions it includes are true. ISNUMBER checks to see if there's a numeric value in the those fields.
So IF all these fields are numbers, give me the average of this range, otherwise set the value as Incomplete.
=IF(AND(ISNUMBER([Risk Impact- Monetary]@row), ISNUMBER([Risk Impact-Reputational]@row), ISNUMBER([Risk Impact-Operational]@row)), AVG([Risk Impact- Monetary]@row:[Risk Impact-Operational]@row), "Incomplete")
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!
-
Thanks, is there a way to quickly look across or does the ISNUMBER need to be in each cell?
I am trying to simply the longer formula below with 2 columns.
=IF(ISNUMBER([Governance Structure]@row:[Contracting/Vendors]@row), "Done", "Not Done")
=IF(AND(ISNUMBER([Governance Structure]@row), ISNUMBER([Policies and Procedures]@row), ISNUMBER(Communications@row), ISNUMBER(Training@row, ISNUMBER([Screening, Monitoring & Reporting]@row), ISNUMBER(Systems@row), ISNUMBER([Budget and Resources]@row), ISNUMBER([Contracting/Vendors]@row)))))), AVG((([Governance Structure]@row * {Mitigating Controls Weighting Table Range 16}) + ([Policies and Procedures]@row * {Mitigating Controls Weighting Table Range 17}) + ([Communications@row] * {Mitigating Controls Weighting Table Range 18}) + (Training@row * {Mitigating Controls Weighting Table Range 19}) + ([Screening, Monitoring & Reporting]@row * {Mitigating Controls Weighting Table Range 20}) + (Systems@row * {Mitigating Controls Weighting Table Range 21} + ([Budget and Resources]@row * {Mitigating Controls Weighting Table Range 22}) + ([Contracting/Vendors]@row * {Mitigating Controls Weighting Table Range 23})))), "Incomplete")
-
I'm sorry, didn't you ask about checking just 3 cells? 🤣
Ok, here's a different approach:
=IF(COUNTIFS([Governance Structure]@row:[Contracting/Vendors]@row), ISBLANK(@cell)) > 0, "Not Done", "Done")
COUNTIFS will look across the row range and count any blank cells. IF the count is more than 0, it's Not Done, if the count is 0, it's Done.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 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!