Normalized Rank Formula

Hello - I am trying to recreate the formula below in my sheet. However, instead of using tabs in excel (e.g. 'Project Type Weight'!B:B), I am tagging the row and dragging the number of rows down (row1:row100). I am getting either an unparseable or invalid operation error. Can someone please help? 🤣

Excel Formula:

(=ROUND((10/6)*(NUMBERVALUE(C2)/MAX('Project Type Weight'!B:B)+NUMBERVALUE(F2)/MAX('Country Weight'!B:B)+NUMBERVALUE(I2)/MAX('Focus Device Weight'!B:B)+NUMBERVALUE(L2)/MAX('Registration Timeline Weight'!B:B)+NUMBERVALUE(N2)/MAX('Resource Workload Weight'!B:B)+NUMBERVALUE(P2)/MAX('Revenue Impact Weight'!B:B)),0)

Smartsheet Formula:

=ROUND((10 / 6) * ([Project Type Weight]@row / MAX(Weighs1:Weighs10), ([Product Weight Weight]@row / MAX(Weighs1:Weighs10)))

Find more posts tagged with

Formulas

Support

functionality

Accepted answers

All comments

Mark Cronk

Try:

=ROUND((10 / 6) * ([Project Type Weight]@row / MAX(Weight1:Weight10)+ ([Product Weight]@row / MAX(Weight1:Weight10)))

Confirm column name for [weight]. Your formula used [weighs] which could be right.

Mark

Sandra Vulis Petrosyan

Thanks. I am still getting an unparseable error

=ROUND((10 / 6) * ([Project Type Weight]1:[Project Type Weight]10) / MAX (Weights1:Weights10)) + ([Product Weights]1:[Product Weights]10)/MAX(Weights1:Weights10))

Mark Cronk

Hi Sandra,

Your issue is the [Project Type Weight]1:[Project Type Weight]10 portions of your formula. That is a range and needs a function associated.

I need some more information to help sort this out. Can you attach a screenshot of your sheet? Your excel formula uses different weight sets. I'm not clear how you set that up in Smartsheets.

Mark

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.