Percentage Formula - Help Needed

Hi All,

Appreciate someones help on the following formula.

I have 2 columns - 'Estimated Hours' and 'Realized Hours' - I have created a third column 'Task Variance' where I want a percentage value of the difference between Realized Hours (actual work) versus 'Estimated Hours' (planned work time)

What I want to be able to see is that if 'Realized Hours' is less than 'Estimated' it should give me a negative percentage (as in % less than Realized) and if the tasked hours are over, it should show as a positive percentage (as in % greater than Realized).

I can get it to function based on a simple:

=IFERROR(([Est. Hours]@row - [Realized Hours]@row ) / [Est. Hours]@row , "")

But when my Realized are greater than Estimated it gives me a negative percentage.

Find more posts tagged with

Accepted answers

All comments

VBAGuru

Have you tried =[Realized Hours]@row / Estimated@row - 1 and setting the column format to percent?

VBAGuru

@Davyd Oram You could still use the Iferror for when estimated is zero or blank. But above would be your basic formula.

Davyd Oram

https://community.smartsheet.com/discussion/comment/490096#Comment_490096

If i run the formula like this "=IFERROR(([Realized Hours]@row - [Est. Hours]@row ) / [Realized Hours]@row , "-1") i get the following result.

With Estimated Hours at 5 and realized hours at 2.5 - it returns '-100%' when the value should be '-50%'

VBAGuru

@Davyd Oram This is what I get using the formula I provided above:

Davyd Oram

@VBAGuru Appreciate your help on this.

Cant tell if it is syntax in the formula?

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.