Search numbers ending digits in Excel

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
 
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")
 
=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
 
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
 
PM sent

EDIT: is this what you are trying to achieve?
Countif.JPG
 
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
 
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).
 
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
 
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.
 
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
 
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.
 
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

  • New Compressed (zipped) Folder.zip
    9.1 KB · Views: 234
@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

  • techenclave query.zip
    10.5 KB · Views: 199
Last edited by a moderator:
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

  • TE_ASINGH.zip
    9.5 KB · Views: 198
  • TE_ASINGH.zip
    9.8 KB · Views: 209
@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:
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:
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.
 
Back
Top