• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif - trying to count numbers with specific endings

J

JBrauny

New Member
Using excel 2010 and trying to count the number of occurrences of numbers with specific endings in a column.


Column Example:


0.69

1.99

0.54

39.99

39.97


How many times does a number end in .99? If all went well the output would be 2. Been trying to use wildcards- but it doesnt seem to like them.


Help. Thanks. Jay
 
Hi Jay ,


I have Excel 2007 , and the following seems to work , entered as an array formula ( using CTRL SHIFT ENTER ) :


=SUM(IF(MOD(D5:D9*100,100)=99,1,0))


Your data is in cells D5 through D9.


Narayan
 
Jay


Firstly, Welcome to the Chandoo.org Forums


Try the following

Code: 
=SUMPRODUCT(1*(RIGHT(A1:A5,2)="99"))
 
NARAYANK991 & HUI -


Thank you. Just made my life easier & I am reminded that there is always more than one way to solve these problems.


jay
 
Worked on using both formulas last night. Have two different answers.


The sheet column i'm working with is 34K+ lines. All numbers - I am searching for the number of occurances that they end in .99


Using both methods I have different answers.


I have verified formatted as numbers. I did a multiply by 1 to make sure they are numbers.


I then chose a smaller sample of 400 lines to test and count. NARAYANK991's method comes up short on the count. This is the same on the full test -- NARA's comes up short as if it is not counting some of the instances.


I have also expanded the numbers out 6+ decimal places looking for trailing digits if possible - but they've been ok.


I'm thinking the difference is in a formatting issue somewhere?


Any thoughts?
 
Hi Jay ,


The problem may be to do with decimal places , and even 6 may not be sufficient to see the difference ; you can try :


=SUM(IF(MOD(INT(D5:D9*100),100)=99,1,0))


to see whether using the INT function removes the decimals. If the formula still doesn't work , then , as long as Hui's formula works , we'll have to stick to it !


In fact , that is the reason I tried MOD(D5:D9*100,100) ; actually when I tried MOD(D5:D9,1)=0.99 it fell short in certain cases ; 36.99 was being stored as 36.990000000000002 , which was not being treated as equal to 0.99 ! On the small sample that was available , MOD(D5:D9*100,100) worked ; now it looks that given a large enough data set , even this formula will fail.


Narayan
 
NARAYANK991- Thank you.


Without trying to change directions of the thread, the data I am using is exported from another program, and I do have to clean it up before I start working with it.


My precision only requires 2 decimal places. Is there a quick way to cut off anything extra beyond 2 decimal that I could include in my cleaning?


Thanks again!

jay
 
Hi Jay ,


The formula =MOD(INT(A1*100),100) ( where A1 has a decimal number ) should eliminate all digits beyond the 2nd decimal place ; thus 36.997 will become 3699.7 on multiplying by 100 ; taking the INT should give 3699 ; using the MOD function with 100 as the divisor , should bring it back to 36.99


There may be an easier way , but this should do it.


I just checked the help on INT , and there may be a problem in the case of negative numbers ! We'll have to try out some test cases.


Narayan
 
Hi, JBrauny!

Considered using ROUND, ROUNDUP or ROUNDDOWN functions?

All need 2 arguments: number and decimal_places

Here's a sample of the three:

[pre]
Code: 
1,2345	1,23
1,231	1,24
1,236	1,23
[/pre]
Regards!
 
=SUMPRODUCT(1*(MOD(ROUND(ABS(A1:A5),2),1)=0.99))


Tests if the decimal portion of a number rounded to 2 decimal places is .99, regardless of being a positve or negative number.
 
Hi Jay ,


Thanks to SirJB7 ; the ROUNDDOWN function is the right one ; using it ensures that all your input data will have exactly 2 decimal places ; using the INT function creates problems in negative numbers. You need to use the TRUNC function.


Suppose your number is in A1 ; =ROUNDDOWN(A1,2) will keep it to exactly 2 decimal places ; =TRUNC(ABS(100*(ROUNDDOWN(A1,2)-TRUNC(ROUNDDOWN(A1,2))))) will give the exact 2 decimal places ; this can then be compared to 99.


Narayan


P.S. Luke : A value like 36.99 seems to be problematic , since the result is 0.990000000000002 which does not equal 0.99 !
 
Hi, JBrauny!

For the same but negative numbers, there's the table:

[pre]
Code: 
-1,2345   -1,23
-1,231    -1,24
-1,236    -1,23
[/pre]
Check if you need ROUNDDOWN or ROUND.

Regards!


@NARAYANK991

:)
 
Everyone:


Thanks for all the input. Everyone has been right - unfortunately I chose .99 to test everything - which seems to be a problem as Narayan pointed out.


I re-ran the two original suggestions from Narayan and Hui for other endings - .09, .19, .29, .... up to .89. In all these cases the original formulas had the same results, and were correct.


I still have to test truncating or rounding. I believe the answer will lie within them. I did some testing with Hui's solution and found some trailing digits in my data set that didnt get counted; this occurred the larger sample i worked with.


Lesson learned.


I'll definitely be returning for more help. Thanks again :)


jay
 
Hi All


I woke up this morning and saw all the discussions about this problem.


My apologies, as I should have explained why I choose the text route to solve this initially instead of using numbers which have inherent rounding issues.

Using the text route lets you control the accuracy with the Cells Number Format without reverting to Round, Int or Trunc etc.
 
@Hui

Absolutely agree. Just pointed out the ROUND's function family because I guessed -right as I read later- that there was a previous problem with input data. And rounding in any direction first, would allow not only check accurately for X.99 but perform exactly further calculations.

And nothing to apologize :)

Regards!
 
Everyone -


Ended up using =SUMPRODUCT(1*(RIGHT(TEXT(Database!L$2:L$35491,"#.00"),2)="50"))

when I was searching for anything ending in .50 in this example.


jay
 
Is there a quick way of modifying this to count a range of endings? I tried wildcards * # ? with the 50 but to no avail.


Currently just adding arrays to the sumproduct... in this case to add any ending in .50 and .51


=SUMPRODUCT(1*(RIGHT(TEXT(Database!L$2:L$35491,"#.00"),2)="50")+1*(RIGHT(TEXT(Database!L$2:L$35491,"#.00"),2)="51"))
 
Hi Jay ,


Thanks for the feedback. Regarding your question on multiple criteria , such as 50 or 51 , I think whatever formula you have used is the right one.


Narayan
 
You must log in or register to reply here.
Back
Top