I have a data validation that even though it tests out correctly external to the validation is not triggering an error message.
=IF(TRUNC(U86*100,7)-TRUNC(INT(U86*100),7)=0,IF(S86<>0,IF(S86=U86,1,0),1),0)
S = Budget Amount
U = Actual Amount
The premise is that the actual amount must always be US dollars and cents with no fractional cents (Not 100.501 or such) and if there is a budget amount the actual amount must be equal to the budget amount. Any false will set the result to zero (0) which should trigger the validation to produce an error message.
The validation formula works when there is a budget amount and the actual amount <> the budget amount but when there is NO budget and the actual contains a fractional cent ( 100.501) the formula fails even though the resulting value of the test is FALSE.
Please, show me what is wrong. I've worked and tested till green in the face on this one.
I'm truncating the decimal test at 7 digits because there are times where Excel miscalculates the fractions and out that far is not significant to me.
Thank you for reading this,
TheOldPuterMan AKA John
