Help Needed: Adding or multiply 2 cells with formulas

jmo

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?

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.

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.