Help with failing Data Validation formula

**TheOldPuterMan** · 03-15-2021, 02:20 PM

I have a data validation formula that works perfectly when tested in a cell at the spreadsheet level BUT fails to work when used as validation for column "X".

=IF(OR(TRUNC(X389*100,7)-TRUNC(INT(X389*100),7)<>0,AND(U389<>"",U389<>X389)),0,1)

U389 = Budgeted_Amount
X389 = Actual_Amount

The purpose is to 1) check the entry in "X" for fractional cents and fail if found and 2) to see if there is a budget amount in "U" and if so does the actual in "X" match.

The formula works fine till I insert it as a formula for validation for "X".

I can not send the Workbook as it is very large.

It fails to correctly identify fractional cents in "X" ($12.999) when the budget "U" is a null.

Thanks,

TheOldPuterMan

**FDibbins** · 03-16-2021, 12:22 AM

2 suggestions...
1. Try it without the IF statement (shouldnt really make a difference , but the IF statement is redundant, DV tests for TRUE (1) or FALSE (0) so you dont need to add them
2. Try each part on it's own and see if either 1 fails your test - that may tell you where the fault lies.

**bebo021999** · 03-16-2021, 12:33 AM

OR(cell 1, cell 2) gives single "TRUE" or "FALSE"

OR(range 1, range 2) gives single "TRUE" or "FALSE" too, not range of TRUE or FALSE

I'd suggest SUMPRODUCT function.

Try to post may be a few rows/columns of sample, not all.