Search numbers ending digits in Excel

shravank30

Active Member
Adept
Aug 29, 2009
750
21
32
39
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

Thanks for advising
 

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
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
Adept
Aug 29, 2009
750
21
32
39
=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

Thanks for advising
 

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
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
Adept
Aug 29, 2009
750
21
32
39
You need to first extract the digits,

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

Nope
Extracted the digits as advised

Answer still 0
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

Please advise alternative solution
 

shravank30

Active Member
Adept
Aug 29, 2009
750
21
32
39
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
Thanks for advising
 

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
253
New Delhi
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
Adept
Aug 29, 2009
750
21
32
39
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

Thanks for advising
 

Criminal

Well-Known Member
Veteran
Jun 6, 2013
921
722
126
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
Adept
Aug 29, 2009
750
21
32
39
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.

Did as advised
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
Jun 6, 2013
921
722
126
Did as advised
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
Adept
Aug 29, 2009
750
21
32
39
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

Thanks for advising
 

Attachments

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
@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

Last edited by a moderator:

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
253
New Delhi
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

Thanks for advising
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

Criminal

Well-Known Member
Veteran
Jun 6, 2013
921
722
126
@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:

Spy king

Well-Known Member
Adept
Oct 16, 2005
660
11
82
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.
Yep! Sumproduct is an awesome function!
I think in this case, @asingh approach of B1-Trunc(B1) to return the decimal + Countif() will be easier/simpler!
 
Last edited by a moderator:

asingh

Well-Known Member
Super Mod
Jun 13, 2009
6,513
1,260
253
New Delhi
See my 2nd attachment. That is the most dynamic solution, without using VBA. It pulls out the decimal, creates a unique list of decimals and then pulls the respective counts. It can be easily expanded beyond 115 rows.