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

Answers

  • Jeff Reisman
    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")

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    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!

  • MJ215
    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
    Jeff Reisman ✭✭✭✭✭✭
    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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!