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

• ✭✭✭✭✭✭

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!