@shravank30 updated the formula. You need to populate (drag) the "Decimals" column if you add more stuff to column B.
Attachments

10.7 KB Views: 78
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.
@shravank30 updated the formula. You need to populate (drag) the "Decimals" column if you add more stuff to column B.
Which is cool. At times array functions are necessary, and it is the developers job to make it dynamic as possible or keep the rowdepth to qualify for 'allneeds'. I personally never hardcode in values no matter what, but this was a RAD so what you did is superb. As I said, it is an elegant solution...!@asingh I use arrays for all of my personal excel work but stick to simple formulas if an excel file is supposed to be shared with others.
I counted values in the increment of 0.5 as it was OP's requirement.
I guess, I missed that. Nice. Thanks.I think the original data is in multiples of 5 paise as mentioned in op!
Could you explain for further use how you set up your sheet?Mmm. @gauravH 's solution is elegant. Make sure to add up the counts of rows and occurrences to make sure you are catching all decimals. If you have something not in the range of .05, it might not work. Change B2 to "108.11"; and the count will get less. So keep rechecking.Else the count list should be expanded for all values possible, or I can update my sheet for 1000 row depth.
Could you explain for further use how you set up your sheet?
Mainly,Yea, sure. Ask what you are notclear about. I can tell, of course.
Mainly,
1. Checking for unique digits ( SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)
What exactly does this do, If Countif = 1, then ? I am a little confused with SUMPRODUCT, doesn't it require a range?
2. Extraction of all the unique digits
It will flag the last occurrence of and entity in a group of cells, in a distinct range. If you notice the start cell C2; it is moving and dynamic. So if it encounters a same value in the same range, it will return a zero, but when it hits the last possibility, it will return the 1. This way, I am demarking...what is unique (with a 1)...as per the last occurrence. No, SUMPRODUCT (SM) does not require a range, just an array. So I get to multiply the resultant (an array) of the COUNTIF using the SM logic to return a 0,1 ...i.e. boolean. That is how the processing works.=IF(SUMPRODUCT((COUNTIF(C2:$C$115,C2)=1)*1)=0,"",1)
You see it 2x in there, cause the first time round it is used to trap any errors, and thereon return clean values. You have to understand how array (CSE) functions work. Basically it is looping through the range, looking for "1", and placing that value in the cell it is placed in. It creates an array matrix within it self, and plugs it on the cell it has been CSE'ed on..! Basically it looks for a 1 (in an array); and then picks up the row value where that 1 was found (in the same array); and using INDEX, extract the equivalent value from the left of the array (but with the same range). This way it cycles through the whole range, and picks up...all the values which were flagged with a "1" using the above. Beautiful...!INDEX(C$2:C$115,SMALL(IF($D$2:$D$115=1,ROW($C2:$C115)1,""),ROW($C2:$C115)1)