Average Across ONLY IF NOT Blank Going Across Grid.

MJ215
MJ215 ✭✭
in Formulas and Functions

Hi,

How can i keep a cell at "Incomplete" until all 3 scores are entered going across.

image.png

=IFERROR(AVG([Risk Impact- Monetary]@row:[Risk Impact-Operational]@row), "Incomplete")

· Share on FacebookShare on Twitter

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

    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!

    · Share on FacebookShare on Twitter
  • 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")

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    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.

    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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!