Help Needed: Adding or multiply 2 cells with formulas

Hi team - I have a simple 4 column sheet I'm trying to test nested IF functions returning a predefined answer and then sum up or multiply the 2 "scores" together:

Impact options:

No impact, service continues at full capacity

Service is limited to 75% capacity available

Service is limited to 50% capacity available

Service is limited to 25% capacity available

Services is not available – 0% available

Impact score formula - this works: =IF(Impact@row = "No impact, service continues at full capacity", "1", IF(Impact@row = "Service is limited to 75% capacity available", "2", IF(Impact@row = "Service is limited to 50% capacity available", "3", IF(Impact@row = "Service is limited to 25% capacity available", "4", IF(Impact@row = "Services is not available – 0% available", "5")))))

Risk options:

Low risk, supplier service is not operationally significant

Med risk, could experience delay in operations or deliverables of an operationally significant service

High risk, could experience service outage of a operationally significant service

Risk score formula - this works: =IF(Risk@row = "Low risk, supplier service is not operationally significant", "1", IF(Risk@row = "Med risk, could experience delay in operations or deliverables of an operationally significant service", "2", IF(Risk@row = "High risk, could experience service outage of a operationally significant service", "3")))

HELP NEEDED:

First row: The Total score column will not sum up when I use the following formula, it returns 0: =SUM([Impact score]@row + [Risk score]@row)

Second row: The Total score column will not provide a product when i multiply Impact score by Risk score: =[Impact score]@row * [Risk score]@row ; this returns an #INVALID OPERATION error

What am I missing?

Find more posts tagged with

Accepted answers

David Joyeuse

=VALUE([Impact score]@row)+ VALUE([Risk score]@row)

=VALUE([Impact score]@row) * VALUE([Risk score]@row)

works fine too :)

I always found it to be good practice to use the function VALUE when doing maths instead of just typing in the cell name.

All comments

Courtney Caswell

Without testing it,

Would =SUM([Impact score]@row*[Risk score]@row) fix it?

jmo

@Courtney Caswell - that formula gave the same INVALID OPERATION error

L_123

you have a datatype issue. You are wrapping your integer returns in quotes ( like this: "2") which tells smartsheet they are text. Delete the quotes and it will work.

David Joyeuse

=VALUE([Impact score]@row)+ VALUE([Risk score]@row)

=VALUE([Impact score]@row) * VALUE([Risk score]@row)

works fine too :)

I always found it to be good practice to use the function VALUE when doing maths instead of just typing in the cell name.

jmo

That the stuff, @David Joyeuse !!!!

Appreciate your speedy response.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.