Archived 2016 Posts
Archived 2016 Posts
Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Equal = sign is not correctly calculating in formula, incorrectly determining <>
I have create a column to evaluate whether enough resources have been allocated to a task. It evaluates whether the percentage in the % Allocated column is equal to, greater than, or less than a second %Allocated column, which uses a formula to determine desired allocated based on workhours and task duration.
[%Allocated] =IF([Estimated Duration]1 <> 0, [Estimated Hours]1 / ([Estimated Duration]1 * 7.5), 0)
(Got that formula from the community -- thanks!)
It seems that no matter how I structure the IF statement, the formula misses when in fact [% Allocated] = [%Allocated]. Sometimes it determines (incorrectly!) that one value is greater than the other, sometimes the other way around with seemingly no rhyme or reason. Here's what I've tried:
=IF(%Allocated1 = [% Allocated]1, "Sideways", IF(%Allocated1 > [% Allocated]1, "Up", IF(%Allocated1 < [% Allocated]1, "Down")))
=IF(%Allocated1 = [% Allocated]1, "Sideways", IF(%Allocated1 > [% Allocated]1, "Up", "Down"))
=IF(%Allocated1 > [% Allocated]1, "Up", IF(%Allocated1 = [% Allocated]1, "Sideways", "Down"))
I have tried reordering the conditions in the formula with no luck.
Why is this happening??
Comments
-
AH,
As one column is the assigned resource management column and is entered and the other is calculated the only thing that springs to mind is the number of decimal points. i.e. if the calc come out as .341 it may show as 34% and there for not be equal to the 34% you entered on the % allocated column by 0.1 of a percent.
-
I think James got it.
Try using ROUND(cell, 2) around both sides of your comparison to verify.
Craig
-
It worked! Thank you both.
-
I think I have a similar issue when comparing two percentages in this formula: =IF([ACTUAL project margin]1 >= [PLANNED project margin]1, 1, 0). How exactly do I apply the rounding you suggest above?
-
Like this:
=IF(ROUND( [ACTUAL project margin]1,2) >= ROUND([PLANNED project margin]1, 2), 1, 0)
I hope this helps.
Craig
-
Craig, you utter legend - that's sorted it :-)
-
I'm blushing. Glad I could help.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives