Formula to Calculate Total Score

Options

Hello,

I am trying to resolve a formula to calculate a total score in a form for weighted values based on selected criteria. It works in excel when I create a table but not working in smartsheet form and getting #unparseable error. I have 5 criteria each with 5 option to select from. Each option returns a weighted value to adjust the score based on what is selected. Here is the formula I am using in excel that works but without table will not work in Smartsheet:

=IF(Table1[@Compliance]="",0,LEFT(Table1[@Compliance],1)*4+LEFT(Table1[@[Business Strategy]],1)*4+LEFT(Table1[@[Hard Cost Savings]],1)*5+LEFT(Table1[@[IT Strategy]],1)*4+LEFT(Table1[@[Revenue Generated]],1)*6)

Here are the criteria and options:

Here are the values of the options:

Any ideas or suggestions would be greatly appreciated.

Thx,

Jeff

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    This should work for you:

    =IF(Compliance@row = "", 0, VALUE(LEFT(Compliance@row, 1)) * 4 + VALUE(LEFT([Business Strategy]@row, 1)) * 4 + VALUE(LEFT([Hard Cost Savings]@row, 1)) * 5 + VALUE(LEFT([IT Strategy]@row, 1)) * 4 + VALUE(LEFT([Revenue Generated]@row, 1)) * 6)
    

    =IF(Compliance@row = "", 0, VALUE(LEFT(Compliance@row, 1)) * 4 + VALUE(LEFT([Business Strategy]@row, 1)) * 4 + VALUE(LEFT([Hard Cost Savings]@row, 1)) * 5 + VALUE(LEFT([IT Strategy]@row, 1)) * 4 + VALUE(LEFT([Revenue Generated]@row, 1)) * 6)

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    This should work for you:

    =IF(Compliance@row = "", 0, VALUE(LEFT(Compliance@row, 1)) * 4 + VALUE(LEFT([Business Strategy]@row, 1)) * 4 + VALUE(LEFT([Hard Cost Savings]@row, 1)) * 5 + VALUE(LEFT([IT Strategy]@row, 1)) * 4 + VALUE(LEFT([Revenue Generated]@row, 1)) * 6)
    

    =IF(Compliance@row = "", 0, VALUE(LEFT(Compliance@row, 1)) * 4 + VALUE(LEFT([Business Strategy]@row, 1)) * 4 + VALUE(LEFT([Hard Cost Savings]@row, 1)) * 5 + VALUE(LEFT([IT Strategy]@row, 1)) * 4 + VALUE(LEFT([Revenue Generated]@row, 1)) * 6)

  • JeffV
    JeffV ✭✭
    Options

    Thank you Devin. That worked!!

  • JeffV
    JeffV ✭✭
    Options

    Hello I am following up on above request. I am getting an unparseable error with this formula:

    I am trying score these categories:

    Any suggestions?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @JeffV

    Your formula structure works for me without error!

    =IF(Compliance@row = "", 0, VALUE(LEFT(Compliance@row, 1)) * 4 + VALUE(LEFT([Strategic Alignment]@row, 1)) * 4 + VALUE(LEFT([Cost Savings]@row, 1)) * 5 + VALUE(LEFT([Revenue Generated]@row, 1)) * 6)


    I've just double checked your syntax and it looks like you have an additional opening parentheses that's causing the error.

    See where you have:

    VALUE(LEFT(([Cost Savings]@row, 1)) * 5

    ^ There's an extra ORANGE ( in front of "Cost Savings" which also happens to be orange (easy to miss!)

    Try deleting that out so it shows:

    VALUE(LEFT([Cost Savings]@row, 1)) * 5

    That should fix it!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!