Normalized Rank Formula

Options

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

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Sandra Vulis Petrosyan
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!