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

BillTony

Board Regular
Joined
May 3, 2017
Messages
64
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hallo,

try something like this:

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

regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,493
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)
and then put this formula in C1 to get what you want:
Code:
=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:
Code:
=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

Board Regular
Joined
May 3, 2017
Messages
64
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,493
Office Version
  1. 365
Platform
  1. Windows
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,119
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
BTW, you could wrap it all up in one nasty-looking formula (without a helper columns) like this:
Code:
=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.
 
Last edited:

Forum statistics

Threads
1,186,139
Messages
5,956,124
Members
438,234
Latest member
Sp922

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top