Error checking of digits / integers to right of decimal point.

[BillTony]

Hi there,

I coding a project and having a problem with "counting" the integers to the right of the decimal point in a given cell.

In a nutshell...

1. Blank = Error.
2. na = Correct.
3. 1.5 = Error. Must have between 2 and 4 (inclusive) digits to the right of the decimal point.
4. 7.62 = Correct.
5. 5.96317 = Error. Too many digits to right.

I have no limit to the number of digits to the left of the decimal point (e.g., 9999999999999.965 would be correct, while 9999999999999.7 would be an error).

Thanks in advance!

 

[Fennek]

Hallo,

try something like this:

d = val(mid(cells(i,j).text, instr(1,cells(i,j),".")))
if d > 1 and d < 5 msgbox "ok"

regards

 

[Joe4]

Are you looking for a VBA or a formulaic solution?
Using a helper column, you can use this formulaic solution.
For an entry in cell A1, put this formula in B1 to count the number of characters after the decimal point:

=IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)

and then put this formula in C1 to get what you want:

=IF(A1="na","Correct",IF(AND(B1>=2,B1<=4),"Correct","ERROR"))

BTW, you could wrap it all up in one nasty-looking formula (without a helper columns) like this:

=IF(A1="na","Correct",IF(AND(IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)>=2,IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)<=4),"Correct","ERROR"))

 

[BillTony]

VBA, unfortunately...

A little background is that this will be integrated into an ongoing or adaptive dataset used for multiple projects with varying requirements - coded in VBA - which, unfortunately change radically over time.

Helper columns, while obviously useful, will simply increase the size of a file which is already unwieldy!

Any help is ALWAYS appreciated.

 

[Joe4]

OK. I did also give you a solution WITHOUT helper columns, and Fennek gave you a VBA solution.
Does either of those work for you?

 

[BillTony]

Yes, thanks.

Your solution worked quite well!

 

[Rick Rothstein]

BTW, you could wrap it all up in one nasty-looking formula (without a helper columns) like this:

=IF(A1="na","Correct",IF(AND(IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)>=2,IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)<=4),"Correct","ERROR"))

I think this shorter, slightly less nasty-looking formula will produce the same results...

=IF(A1="na","Correct",IF(ABS(LEN(0+A1)-LEN(INT(0+A1))-4)<=1,"Correct","ERROR"))

Its main relationship test is built around this generalized relationship for testing whether a value X lies within or on a range of values starting at value A and ending at value B...

ABS(X - ((A + B) / 2)) < ABS(B - A) / 2

where LEN(0+A1)-LEN(INT(0+A1))-1 is what X equals in the generalized relationship, A equals 2 and B equals 4.