Data validation not functioning the same as a test formula

[TheOldPuterMan]

I have a puzzling situation on Data Validation. I have a formula to test the contents of two cells against each other and against other criteria. When I test the formula outside of data validation it responds as expected to changes in the data cells. BUT when I copy that formula into the data validation for the cell involved it fails to perform as expected.

The two cells are Budget Amount and Actual Amount. If Budget Amount is blank I simply test the Actual Amount to make sure there are only 2 decimal places to the right of the dot. 

If, however, the Budget has a value in it I test to see if the Actual Amount is equal to the Budget Amount and then test for decimal places to the right.

Below is my formula where CA is the Budgeted Amount and CB is the Actual Amount.

=IF(OR(AND(CA14="",CB14*100-INT(CB14*100)=0),AND(CA14=CB14,CB14*100-INT(CB14*100)=0)),1,0)

This formula works 100% of the time when tested external to the Data Validation BUT fails every time that the Budgeted Amount cell is empty (Blank) and the Actual Amount has more than 2 decimal places when used as a formula in Data Validation

.

Any suggestions would be appreciated. I'm out of ideas. (BTW, Tried MOD but it is too quirky to rely on).

Thanks,

TheOldPuterMan

[#2]

@TheOldPuterMan Not sure what you try to achieve. When I break down the formula I understand the following.

AND(CA14="",CB14*100-INT(CB14*100)=0)

This will return TRUE if CA is empty and CB has up to two (i.e. 0,1 or 2) decimals.

AND(CA14=CB14,CB14*100-INT(CB14*100)=0)

This will return TRUE if CA is equal to CB and CB has up to two (i.e. 0,1 or 2) decimals.

Overall, the formula returns 1 if either of the two above are TRUE, otherwise it returns 0. Is this what you intend to achieve?

The issue that remains is that you want to use this in a formula within Data Validation (DV). I pasted the exact formula into a custom DV rule and it would allow everything when CA was empty. When CA was not empty it only allowed me to enter the exact same number in CB, but only if it did not have more than two decimals. This doesn't make any sense to me.

Now, you could enter the following formula in a custom DV rule. It will allow any number with up to two decimals, irrespective what's in CA.

=ISNUMBER(CB14)*(LEN(CB14)-IFERROR(FIND(".",CB14),LEN(CB14))<=2)

If your actual numbers are picked up by a formula, rather than by direct input in CB, you could wrap that formula in a ROUND function that rounds all numbers to 2 decimals). Then there is no need for DV doing the testing for you. But perhaps I'm missing the point completely here.