Equal = sign is not correctly calculating in formula, incorrectly determining <>

AH

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

JamesR

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.

J. Craig Williams

I think James got it.

 

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

 

Craig

AH

It worked! Thank you both.

Becky Swanwick

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?

J. Craig Williams

Like this:

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

I hope this helps.

Craig

Becky Swanwick

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

J. Craig Williams

I'm blushing. Glad I could help.

Craig