Strange results for nested IF after data refresh

[Concetta]

Hello all - I have an odd situation - making it even odder is the fact that a google search doesn't produce a million people having the same problem.

I have a calculated column with the following formula to identify outliers in contract values:

=if(deal[avg_price]<480,1,if(deal[avg_price]>14800,1,0))

I am attempting to identify (with a "1") any contracts that fall below $480 or above $14,800 as an outlier.

The formula only sort of works - when I refresh the underlying data (e.g., new month), the formula yields weird results - "1" where it is clearly a "0" and vice versa, but not in every case - only some are wrong - no logic. If I go in and add something to the formula (an extra character, for example), hit enter, get an sytax error message, and then change the formula back (delete the extra character), the results are then accurate.

This worries me. I have had odd results with other nested IF statements, as well.

Thank you, in advance, for your insight.

 

[Jacob Barnett]

It's funny but until I read your post I'd never noticed an issue with nested IF()s!

Not sure whether it entirely explains your issue but the following formula behaves totally differently in PowerPivot than it does in Excel:

=IF([Week Commencing]="25/01/2013",4,IF([Week Commencing]="04/02/2013",11,IF([Week Commencing]<="06/05/2013",14,BLANK())))

In PowerPivot the 3rd IF() seems to override the previous IF()s which is definitely not the case in Excel where the formula moves to the end of the IF once the condition is satisfied. My guess is that not only is this a bug potentially yielding the 'wrong' answer but that it must have a negative effect on performance as it evaluates every criteria regardless.