Erratic data validation formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1676325%22%20slang%3D%22en-US%22%3EErratic%20data%20validation%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1676325%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20validation%20that%20even%20though%20it%20tests%20out%20correctly%20external%20to%20the%20validation%20is%20not%20triggering%20an%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(TRUNC(U86*100%2C7)-TRUNC(INT(U86*100)%2C7)%3D0%2CIF(S86%26lt%3B%26gt%3B0%2CIF(S86%3DU86%2C1%2C0)%2C1)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ES%20%3D%20Budget%20Amount%3C%2FP%3E%3CP%3EU%20%3D%20Actual%20Amount%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20premise%20is%20that%20the%20actual%20amount%20must%20always%20be%20US%20dollars%20and%20cents%20with%20no%20fractional%20cents%20(Not%20100.501%20or%20such)%20and%20if%20there%20is%20a%20budget%20amount%20the%20actual%20amount%20must%20be%20equal%20to%20the%20budget%20amount.%20Any%20false%20will%20set%20the%20result%20to%20zero%20(0)%20which%20should%20trigger%20the%20validation%20to%20produce%20an%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20validation%20formula%20works%20when%20there%20is%20a%20budget%20amount%20and%20the%20actual%20amount%20%26lt%3B%26gt%3B%20the%20budget%20amount%20but%20when%20there%20is%20NO%20budget%20and%20the%20actual%20contains%20a%20fractional%20cent%20(%20100.501)%20the%20formula%20fails%20even%20though%20the%20resulting%20value%20of%20the%20test%20is%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%2C%20show%20me%20what%20is%20wrong.%20I've%20worked%20and%20tested%20till%20green%20in%20the%20face%20on%20this%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20truncating%20the%20decimal%20test%20at%207%20digits%20because%20there%20are%20times%20where%20Excel%20miscalculates%20the%20fractions%20and%20out%20that%20far%20is%20not%20significant%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20reading%20this%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20AKA%20John%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1676325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1676595%22%20slang%3D%22en-US%22%3ERe%3A%20Erratic%20data%20validation%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1676595%22%20slang%3D%22en-US%22%3EI'm%20not%20sure%20I%20understand%20this%20part%3A%20TRUNC(INT(U86*100)%2C7)%3CBR%20%2F%3EINT%20is%20going%20to%20round%20down%20to%20the%20nearest%20integer%2C%20so%20trunc%20is%20not%20going%20to%20do%20anything.%3CBR%20%2F%3E%3CBR%20%2F%3ETRUNC(100.501)%20%3D%20100.501%2C%20but%20INT(100.501)%3D100.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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

1 Reply
Highlighted
I'm not sure I understand this part: TRUNC(INT(U86*100),7)
INT is going to round down to the nearest integer, so trunc is not going to do anything.

TRUNC(100.501) = 100.501, but INT(100.501)=100.