# Search numbers ending digits in Excel

#### shravank30

##### Active Member
Hi

I want to find out how many times a number ends in .05, .10 , .15 etc. in the excel sheet
As the data runs in thousands it is not possible to do it manually

The excel sheet has number like 10.05, 65.35, 75.80 etc. (all multiples of 5 paisa)
I shall be grateful if the members can give me the solution

#### Spy king

##### Well-Known Member
Hi,

See if COUNTIF along with a check condition to see if the number after the decimal place is equal to what you want to check

To extract digits: Mod(A1,1)
To count cases: Countif(range, "0.15")

#### shravank30

##### Active Member
=COUNTIF(B1:B251,"*05")

Gave the following command, but the answer was 0 (The entire data is in B Column)

Can you please give me the correct formula

#### Spy king

##### Well-Known Member
You need to first extract the digits,

So, in a Column C, try =Mod(B1,1)
Then try =Countif(C1:C251,"0.05")

#### shravank30

##### Active Member
You need to first extract the digits,

So, in a Column C, try =Mod(B1,1)
Then try =Countif(C1:C251,"0.05")

Nope

If you can PM me your email id, I can email you the excel sheet.
The system is not allowing me to upload the .xls sheet on the site

#### Spy king

##### Well-Known Member
PM sent

EDIT: is this what you are trying to achieve?

#### shravank30

##### Active Member
Excel sheet sent on the email id

If you can find the solution, I shall be grateful if you send the correct formula in the sheet with all the data as it is in return mail

#### asingh

##### Well-Known Member
Super Mod
Hi

I want to find out how many times a number ends in .05, .10 , .15 etc. in the excel sheet
Basically you want to check..if a decimal exists after the whole number..??? Since it can be any thing after the decimal..?

If that is the logic you can:

1. Round the numbers.
2. Subtract the rounded number from the original number.
3. If there is a +-ve delta (count it).
4. If there is not a +-ve delta (do not count it).

#### shravank30

##### Active Member
Basically you want to check..if a decimal exists after the whole number..??? Since it can be any thing after the decimal..?

If that is the logic you can:

1. Round the numbers.
2. Subtract the rounded number from the original number.
3. If there is a +-ve delta (count it).
4. If there is not a +-ve delta (do not count it).

Hi

You are getting me wrong.
I want to know how many numbers in the entire database end with .05, how many with .010 and so on to see which number occurs maximum times

Veteran

#### Attachments

• 4.9 KB Views: 82
Last edited by a moderator:

#### shravank30

##### Active Member
PM sent

EDIT: is this what you are trying to achieve?
View attachment 22750
Absolutely correct[DOUBLEPOST=1371201643][/DOUBLEPOST]
As soon as I substituted my data, all became 0
You have hidden column F

Do I have to do some more cut paste of the formulas ?

Last edited by a moderator:

#### Criminal

##### Well-Known Member
Veteran
As soon as I substituted my data, all became 0
There could be only one reason that all data becomes zero - your data is not in "number" format. When you copy-paste your data, it gets converted to "text". Try typing few values manually in this sheet. If it works, then while pasting your data, you need to use "right click" - "paste - as" option and select "values".

Unhide all the columns and modify the formula to span across all the data. Just drag the last cell to auto-populate formulas in the column.

#### shravank30

##### Active Member
There could be only one reason that all data becomes zero - your data is not in "number" format. When you copy-paste your data, it gets converted to "text". Try typing few values manually in this sheet. If it works, then while pasting your data, you need to use "right click" - "paste - as" option and select "values".

Unhide all the columns and modify the formula to span across all the data. Just drag the last cell to auto-populate formulas in the column.

I am getting the correct answer for only 3 values 0.25, 0.50 & o.75.. all other are values are o although they also occur in similar numbers

#### Criminal

##### Well-Known Member
Veteran
I am getting the correct answer for only 3 values 0.25, 0.50 & o.75.. all other are values are o although they also occur in similar numbers
Extend the formula all the way to the bottom in column F. If that fails, send me your file after removing any sensitive data.

#### shravank30

##### Active Member
Extend the formula all the way to the bottom in column F. If that fails, send me your file after removing any sensitive data.
Its not a sensitive data file
Its for statistical purposes
I have zipped and attached the file so that you can download it
I shall be grateful if you can correct the formula and repost it

#### Attachments

• 9.1 KB Views: 78

#### Spy king

##### Well-Known Member
@gauravH

I am having similar issues, if the data that countif is searching for are direct values, then it seems to work flawlessly. But when it's a result of a formulae, it seems to have issues.

After some more investigation I think I know what the issue is. When you use MOD(B1,1) to get 0.10, the actual value is 0.999999999999 hence when we use countif, it doens't pick it up. hence the actual formulae should be =Round(Mod(A1,1),2)

I hope I am making sense?

EDIT:

Added the corrected file that @shravank30 mailed to me.

#### Attachments

• 10.5 KB Views: 76
Last edited by a moderator:

#### asingh

##### Well-Known Member
Super Mod
Hi

You are getting me wrong.
I want to know how many numbers in the entire database end with .05, how many with .010 and so on to see which number occurs maximum times

Attached is a file.

1. You paste your data in column B. Will run for 115 rows only.
2. And let the sheet calculate
3. It will pick up the decimals.
4. Create a unique list.
5. Show the count in "F".

Let me know if this works, can help you expand it. Cause your decimal can be anything.[DOUBLEPOST=1371204853][/DOUBLEPOST]Corrected file. One error.

#### Attachments

• 9.5 KB Views: 73
• 9.8 KB Views: 79

#### Criminal

##### Well-Known Member
Veteran
@gauravH

Round(Mod(A1,1),2)

I hope I am making sense?
Yes, you are correct. There are so many ways to approach this. Usually, I go with arrays but it would be difficult for others to modify them. Now, I am using "value" to work on absolute values . That unique number thing by @asingh would also work.

Last edited by a moderator: