Search numbers ending digits in Excel

Status
Not open for further replies.
shravank30

shravank30

Discoverer
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
 
Spy king said:
You need to first extract the digits,

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


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
 
shravank30 said:
Hi

I want to find out how many times a number ends in .05, .10 , .15 etc. in the excel sheet
Click to expand...

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).
 
asingh said:
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).
Click to expand...


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
 
shravank30 said:
As soon as I substituted my data, all became 0
Click to expand...

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.
 
gauravH said:
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.
Click to expand...


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
 
shravank30 said:
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
Click to expand...
Extend the formula all the way to the bottom in column F. If that fails, send me your file after removing any sensitive data.
 
gauravH said:
Extend the formula all the way to the bottom in column F. If that fails, send me your file after removing any sensitive data.
Click to expand...

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

@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:
shravank30 said:
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
Click to expand...

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

Spy king said:
@gauravH

Round(Mod(A1,1),2)

I hope I am making sense?
Click to expand...

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:
gauravH said:
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.
Click to expand...

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.
 
Status
Not open for further replies.
Top Bottom