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

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

I think James got it.

Try using ROUND(cell, 2) around both sides of your comparison to verify.

Craig

• Options

It worked! Thank you both.

• Options

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?

• ✭✭✭✭✭✭
Options

Like this:

=IF(ROUND( [ACTUAL project margin]1,2) >= ROUND([PLANNED project margin]1, 2), 1, 0)

I hope this helps.

Craig

• Options

Craig, you utter legend - that's sorted it :-)

• ✭✭✭✭✭✭
Options

I'm blushing. Glad I could help.

Craig

This discussion has been closed.