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?
Best Answer

=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.
Answers

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

@Courtney Caswell  that formula gave the same INVALID OPERATION error

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.

=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.

That the stuff, @David Joyeuse !!!!
Appreciate your speedy response.
Help Article Resources
Categories
Check out the Formula Handbook template!