Math formulas across multiple cells

Kathryn Pineda

Hello all,

Any help on the following issue would be very much appreciated. I have a scoring matrix built in to Smartsheet (See below). I would like to multiply the score of each cell for each criteria (Strategic Init Impact Score, Business Impact, etc.) by the corresponding weight (and divide each by 100) and then add those 7 scores together to find the final score. For reference the scoring criteria follows.

3/Yes - 100%

2 - 66%,

1 - 33%;

0/No - 0%

First row manually calculated as: (100*15/100) + (100*25/100) + (100*15/10) + (100*10/100) + (100*10/100) + (0*10/100) + (100*15/100) = Total Score of 90 (the 100 is a typo)

A million thanks in advance!! (I hope this is possible as I have about 250 items to score :))

** Note - the Total Scores already calculated were done by hand **

Bassam Khalil

Hi @Kathryn Pineda 

Hope you are fine, Please try the following formula and convert it to column format formula:

=IFERROR(IF(OR([Criteria 1]@row = 3, [Criteria 1]@row = "Yes"), 15, IF([Criteria 1]@row = 2, 
15 * 0.66, IF(OR([Criteria 1]@row = 1, [Criteria 1]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 2]@row = 3, [Criteria 2]@row = "Yes"), 25, IF([Criteria 2]@row = 2, 25 * 0.66, 
IF(OR([Criteria 2]@row = 1, [Criteria 2]@row = "No"), 0.33 * 25))) + 
IF(OR([Criteria 3]@row = 3, [Criteria 3]@row = "Yes"), 15, IF([Criteria 3]@row = 2, 
15 * 0.66, IF(OR([Criteria 3]@row = 1, [Criteria 3]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 4]@row = 3, [Criteria 4]@row = "Yes"), 10, IF([Criteria 4]@row = 2, 10 * 0.66, 
IF(OR([Criteria 4]@row = 1, [Criteria 4]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 5]@row = 
3,[Criteria 5]@row = "Yes"), 10, IF([Criteria 5]@row = 2, 10 * 0.66, IF(OR([Criteria 5]@row = 1
,[Criteria 5]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 6]@row = 3, [Criteria 6]@row = "Yes"),
10, IF([Criteria 6]@row = 2, 10 * 0.66, IF(OR([Criteria 6]@row = 1, [Criteria 6]@row = "No"),
0.33 * 10))) + IF(OR([Criteria 7]@row = 3, [Criteria 7]@row = "Yes"), 15, 
IF([Criteria 7]@row = 2, 15 * 0.66, IF(OR([Criteria 7]@row = 1, [Criteria 7]@row = "No"),
0.33 * 15))), "")

the Following screenshot shows the result:

Please be sure to use the same columns names in my formula and all columns are test/number type.

Bassam Khalil

@Kathryn Pineda

As i notify you in my post use the same columns name, i fix it as per your columns name this is the rectified formula:

=IF(ISBLANK([CHANGE MANAGEMENT REQS]@row), "", 
IFERROR(IF(OR([Strategic Initiative Impact Score]@row = 3, 
[Strategic Initiative Impact Score]@row = "Yes"), 15, 
IF([Strategic Initiative Impact Score]@row = 2, 15 * 0.66, 
IF(OR([Strategic Initiative Impact Score]@row = 1, [Strategic Initiative Impact Score]@row
="No"), 0.33 * 15))) + IF(OR([BUSINESS VALUE]@row = 3, [BUSINESS VALUE]@row = "Yes"), 25, 
IF([BUSINESS VALUE]@row = 2, 25 * 0.66, IF(OR([BUSINESS VALUE]@row = 1, 
[BUSINESS VALUE]@row = "No"), 0.33 * 25))) + IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 3, 
[NUMBER OF EMPLOYEES AFFECTED]@row = "Yes"), 15, IF([NUMBER OF EMPLOYEES AFFECTED]@row = 2,
15 * 0.66, IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 1, [NUMBER OF EMPLOYEES AFFECTED]@row 
= "No"), 0.33 * 15))) + IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 3,
 [REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "Yes"), 10,
 IF([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 2, 10 * 0.66,
 IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 1, 
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "No"), 0.33 * 10))) + 
IF(OR([REGULATORY COMPLIANCE]@row = 3, [REGULATORY COMPLIANCE]@row = "Yes"), 10,
 IF([REGULATORY COMPLIANCE]@row = 2, 10 * 0.66, IF(OR([REGULATORY COMPLIANCE]@row = 1,
 [REGULATORY COMPLIANCE]@row = "No"), 0.33 * 10))) + IF(OR([RESPONSE TO AUDIT?]@row = 3,
 [RESPONSE TO AUDIT?]@row = "Yes"), 10, IF([RESPONSE TO AUDIT?]@row = 2, 10 * 0.66,
 IF(OR([RESPONSE TO AUDIT?]@row = 1, [RESPONSE TO AUDIT?]@row = "No"), 0.33 * 10))) + 
IF(OR([CHANGE MANAGEMENT REQS]@row = 3, [CHANGE MANAGEMENT REQS]@row = "Yes"), 15, 
IF([CHANGE MANAGEMENT REQS]@row = 2, 15 * 0.66, IF(OR([CHANGE MANAGEMENT REQS]@row = 1, 
[CHANGE MANAGEMENT REQS]@row = "No"), 0.33 * 15))), ""))

Kathryn Pineda

@Bassam Khalil A TREMENDOUS THANKS!!

Bassam Khalil

Hi @Kathryn Pineda 

Hope you are fine, Please try the following formula and convert it to column format formula:

=IFERROR(IF(OR([Criteria 1]@row = 3, [Criteria 1]@row = "Yes"), 15, IF([Criteria 1]@row = 2, 
15 * 0.66, IF(OR([Criteria 1]@row = 1, [Criteria 1]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 2]@row = 3, [Criteria 2]@row = "Yes"), 25, IF([Criteria 2]@row = 2, 25 * 0.66, 
IF(OR([Criteria 2]@row = 1, [Criteria 2]@row = "No"), 0.33 * 25))) + 
IF(OR([Criteria 3]@row = 3, [Criteria 3]@row = "Yes"), 15, IF([Criteria 3]@row = 2, 
15 * 0.66, IF(OR([Criteria 3]@row = 1, [Criteria 3]@row = "No"), 0.33 * 15))) + 
IF(OR([Criteria 4]@row = 3, [Criteria 4]@row = "Yes"), 10, IF([Criteria 4]@row = 2, 10 * 0.66, 
IF(OR([Criteria 4]@row = 1, [Criteria 4]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 5]@row = 
3,[Criteria 5]@row = "Yes"), 10, IF([Criteria 5]@row = 2, 10 * 0.66, IF(OR([Criteria 5]@row = 1
,[Criteria 5]@row = "No"), 0.33 * 10))) + IF(OR([Criteria 6]@row = 3, [Criteria 6]@row = "Yes"),
10, IF([Criteria 6]@row = 2, 10 * 0.66, IF(OR([Criteria 6]@row = 1, [Criteria 6]@row = "No"),
0.33 * 10))) + IF(OR([Criteria 7]@row = 3, [Criteria 7]@row = "Yes"), 15, 
IF([Criteria 7]@row = 2, 15 * 0.66, IF(OR([Criteria 7]@row = 1, [Criteria 7]@row = "No"),
0.33 * 15))), "")

the Following screenshot shows the result:

Please be sure to use the same columns names in my formula and all columns are test/number type.

Kathryn Pineda

Hi Bassam,

Thank you for your help! I copied the formula directly and I am getting an #UNPARESEABLE error...

Kathryn Pineda

Perhaps I just need to adjust my row numbers which I am trying right now. A huge thank you!

Bassam Khalil

@Kathryn Pineda 

Please share me as an admin on a copy of your sheet and i will check what is the problem because it's work on my sample the following is a link to my sample sheet you can check it:

My Email to share: Bassam.k@mobilproject.it

Kathryn Pineda

Will do!

Bassam Khalil

@Kathryn Pineda

No need for row numbers, it's column format formula will work for any number of rows

Kathryn Pineda

@Bassam Khalil I have shared sheet, would you mind taking a look please? Many thanks!

Kathryn Pineda

@Bassam Khalil You are now an admin.

Bassam Khalil

@Kathryn Pineda

As i notify you in my post use the same columns name, i fix it as per your columns name this is the rectified formula:

=IF(ISBLANK([CHANGE MANAGEMENT REQS]@row), "", 
IFERROR(IF(OR([Strategic Initiative Impact Score]@row = 3, 
[Strategic Initiative Impact Score]@row = "Yes"), 15, 
IF([Strategic Initiative Impact Score]@row = 2, 15 * 0.66, 
IF(OR([Strategic Initiative Impact Score]@row = 1, [Strategic Initiative Impact Score]@row
="No"), 0.33 * 15))) + IF(OR([BUSINESS VALUE]@row = 3, [BUSINESS VALUE]@row = "Yes"), 25, 
IF([BUSINESS VALUE]@row = 2, 25 * 0.66, IF(OR([BUSINESS VALUE]@row = 1, 
[BUSINESS VALUE]@row = "No"), 0.33 * 25))) + IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 3, 
[NUMBER OF EMPLOYEES AFFECTED]@row = "Yes"), 15, IF([NUMBER OF EMPLOYEES AFFECTED]@row = 2,
15 * 0.66, IF(OR([NUMBER OF EMPLOYEES AFFECTED]@row = 1, [NUMBER OF EMPLOYEES AFFECTED]@row 
= "No"), 0.33 * 15))) + IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 3,
 [REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "Yes"), 10,
 IF([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 2, 10 * 0.66,
 IF(OR([REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = 1, 
[REQ'D TO MAINTAIN CRITICAL OPERATIONS]@row = "No"), 0.33 * 10))) + 
IF(OR([REGULATORY COMPLIANCE]@row = 3, [REGULATORY COMPLIANCE]@row = "Yes"), 10,
 IF([REGULATORY COMPLIANCE]@row = 2, 10 * 0.66, IF(OR([REGULATORY COMPLIANCE]@row = 1,
 [REGULATORY COMPLIANCE]@row = "No"), 0.33 * 10))) + IF(OR([RESPONSE TO AUDIT?]@row = 3,
 [RESPONSE TO AUDIT?]@row = "Yes"), 10, IF([RESPONSE TO AUDIT?]@row = 2, 10 * 0.66,
 IF(OR([RESPONSE TO AUDIT?]@row = 1, [RESPONSE TO AUDIT?]@row = "No"), 0.33 * 10))) + 
IF(OR([CHANGE MANAGEMENT REQS]@row = 3, [CHANGE MANAGEMENT REQS]@row = "Yes"), 15, 
IF([CHANGE MANAGEMENT REQS]@row = 2, 15 * 0.66, IF(OR([CHANGE MANAGEMENT REQS]@row = 1, 
[CHANGE MANAGEMENT REQS]@row = "No"), 0.33 * 15))), ""))

Kathryn Pineda

@Bassam Khalil A TREMENDOUS THANKS!!

Kathryn Pineda

@Bassam Khalil

Hi Bassam, I am hoping you can help. For some reason the calculations are off. I went in and adjusted a few of the weights in the column formula but still calculations made no sense. I will add you ad an admin and hope you could take a quick look. For example in the first row I have all 0's for each field yet it is still netting a score. in the second row if I select a 2 for first criteria the total score should be 25 and that's not correct. Any help is greatly appreciated!!

Kathryn Pineda

@Bassam Khalil

And just to clarify, the scores in each field (3, 2, 1, Yes or No) correspond to the values below:

CRITERIA SCORE SCORE VALUE

1= 33%

2 = 66%

0/No = 0%

3/Yes = 100%

Kathryn Pineda

I tried to reference the formula above in your sample but the numbers are slightly off there as well. Thank you again.

Bassam Khalil

@Kathryn Pineda 

Fixed please check it.

Kathryn Pineda

@Bassam Khalil Hi Bassam, I think it is close, thank you, but what is confusing me is when all scores are zero the default total remains 9.9....