Normalized Rank Formula

Sandra Vulis Petrosyan

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

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