I need a formula that can simply count the occurances of the decimal value in range (O2-O20)
and write the counts in column Q2 ....see sample
Rep given for a formula that can give those results
Thank you
I need a formula that can simply count the occurances of the decimal value in range (O2-O20)
and write the counts in column Q2 ....see sample
Rep given for a formula that can give those results
Thank you
Can this be done with a formula or do I need a macro ?
Please try at Q2 Press Ctrl+Shift+Enter and drag down
Formula:Please Login or Register to view this content.
Thank you for your effort -It work but not consistently if updated ....added another 1.7 in range O2-O20 so theres 3 and the formula dont update showing 1.7(3) 3 counts.Any idea ?
Still looking for a solution that will update as the data changes
Please try
Formula:Please Login or Register to view this content.
You could do it a lot easier with a helper column.
In P2 and fill down, to give a count of each value in column O
=COUNTIF(O:O,O2)
Then create a pivot table from those 2 columns, with column P as a filter to show only counts greater than 1. Column O as pivottable rows and as a count in the values box, sorted descending.
It's not quite the same visually, but is a lot messy.
See my late reply to your previous question.
You may have entered 1.7 but the other values are 1.66666666666667
Excel can be rather particular at times of comparison.
torachan.
How does it not work?
Untitled.png
Create a named range "MyList" that refers to Range 02:O18
Formula for Q2, Entered using Ctrl Shift Enter
=IF(ROW()>COUNT(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1)),"",SMALL(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1),ROW()-1)& "("&COUNTIF(MyList,SMALL(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1),ROW()-1))&")")
Last edited by mehmetcik; 02-16-2019 at 12:32 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Try this
In Q2
In R2Please Login or Register to view this content.
In S2Please Login or Register to view this content.
Drag down all.Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
If you dont want to use helper columns, In M2 then drag down
Please Login or Register to view this content.
Trying to put the extracted value and the count in the same cell is a bad habit to get into, and like merged cells, totally unnecessary. There are plenty of ways to make it look the same of that is what you want.
In Q2, array confirmed with Shift Crl Enter. Drag down as needed.
=IFERROR(MODE.SNGL(IF(ISNUMBER(MATCH(ROUND($O$2:$O$18,1),Q$1:Q1,0)),"",ROUND($O$2:$O$18,1))),"")
In R2, fill down parallel to the previous formula.
=IF(Q2="","",COUNTIFS(O:O,">="&(Q2-0.05),O:O,"<"&(Q2+0.05)))
If desired, use a custom format of (0) on column R with left alignment or reduced column widths to make it look the same as your example.
And if you do not want your sheet littered with formula try a macro.
The attached works with the range A1:A140 using the Worksheet_Change event.
Enter numbers in this range as you move from the 'entry cell' the 'uniques' alter in column 'B' and their quantity count updates in 'C'
torachan.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks