Formula to Calculate Total Score

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

Find more posts tagged with

Accepted answers

Devin Lee

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)

All comments

Devin Lee

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)

JeffV

Thank you Devin. That worked!!

JeffV

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.