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

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

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

All comments

Amber Eakin

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?

Nick Korna

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

Thank you both so much, Amber & Nick!

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

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.