Math formulas across multiple cells

Options
Kathryn Pineda
Kathryn Pineda ✭✭✭✭
edited 08/01/21 in Formulas and Functions

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 :))

image.png

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

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/01/21 Answer ✓
    Options

    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:


    image.png

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/01/21 Answer ✓
    Options

    @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))), ""))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Answer ✓
    Options

    @Bassam Khalil A TREMENDOUS THANKS!!

    · Share on FacebookShare on Twitter
«1

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/01/21 Answer ✓
    Options

    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:


    image.png

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    Hi Bassam,

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

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

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

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    Will do!

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Kathryn Pineda

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

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

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    @Bassam Khalil You are now an admin.

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/01/21 Answer ✓
    Options

    @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))), ""))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Answer ✓
    Options

    @Bassam Khalil A TREMENDOUS THANKS!!

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    @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!!

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    @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%

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

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

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Kathryn Pineda 

    Fixed please check it.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

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

    · 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!