Help - Trying to Write Simple % Calculation Formula

Options
LDenton
LDenton ✭✭
edited 03/02/23 in Formulas and Functions

Hello everyone!

I'm trying to write formulas to help create visibility into a department's KPIs, which will eventually feed into a Dashboard. I'm fairly new to writing Smartsheet formulas and have been struggling.

I need to reference 2 main columns (Role M1 Actual & Role M1 Goal) in my formula to deposit the result in a 3rd column (Role M1 Error Rate).

I want the formula to calculate a basic % error rate along the lines of Actual / Goal x 100 = %, but so far every variation I've tried has returned #UNPARSEABLE. I'm sure I'm making at least one rookie mistake, but until I understand what I'm doing wrong it's difficult to progress further.

My latest formula attempt: =[(Auditor M1 Actual)/(Auditor M1 Goal)*100]

Does anyone have tips or guidance that could help me move forward? Thank you all for your time and assistance!

Edited to add -- Just tried new variation =([Auditor M1 Actual]:[Auditor M1 Actual] / [Auditor M1 Goal]:[Auditor M1 Goal] * 100) and got the #INVALID OPERATION result this time

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @LDenton,

    If your percentages are by row then this should do what you're after:

    =[Auditor M1 Actual]@row / [Auditor M1 Goal]@row

    In terms of having it display as a percentage, you can just use the formatting options on right hand side of the toolbar:

    Highlight your formula column and when this is pressed (and is highlighted in the screenshot) the results will be in a X% format.

    Hope this helps, if you've any questions or I've misunderstood anything then just post! 😊

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Hello @LDenton - I'd recommend using the formula below and changing the data type in the column to Percentage instead of multiplying by 100. Then, you can right-click on the cell with the formula and make it a Column Formula.

    =[Auditor M1 Actual]/[Auditor M1 Goal]

    Does that work?

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @LDenton,

    If your percentages are by row then this should do what you're after:

    =[Auditor M1 Actual]@row / [Auditor M1 Goal]@row

    In terms of having it display as a percentage, you can just use the formatting options on right hand side of the toolbar:

    Highlight your formula column and when this is pressed (and is highlighted in the screenshot) the results will be in a X% format.

    Hope this helps, if you've any questions or I've misunderstood anything then just post! 😊

  • LDenton
    LDenton ✭✭
    Options

    Thank you both so much, Amber & Nick!

    I made the suggested changes and it is working beautifully now!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!