Average Across ONLY IF NOT Blank Going Across Grid.

MJ215

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")

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320749#Comment_320749

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.

Jeff Reisman

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")

MJ215

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")

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320749#Comment_320749

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.