Help  Trying to Write Simple % Calculation Formula
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
Best Answer

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

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

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! 😊

Thank you both so much, Amber & Nick!
I made the suggested changes and it is working beautifully now!
Help Article Resources
Categories
Check out the Formula Handbook template!