well now for something completely different and utterly boring (but sometimes oh so useful): how do i count the number of unique numbers i have in a set in an excel sheet?
some googling gave me a formula to apply (from http://www.officearticles.com/excel/count_unique_values_in_microsoft_excel.htm) but that didn’t really fly in excel 2007. so i changed it a bit. here is the end result:
=SUM(IF(FREQUENCY(C2:X2; C2:X2)>0;1;0))
Replace C2 and X2 with your range of cells where you want to count the unique numbers in. And yes, perhaps a list should be vertical (in one column) and not horizontal (in one row). but hey.. did the job this time.
Something handy for the road: if the formula fails, excel will highlight the error and put the cursor in that position. now press “function” in the tab/ribbon on the top. you will see what fields it should have and can try to redo the formula to work.
And no, I do not actually know why the formula works.. 🙂 I undertstand it checks if frequency for a value is more than 0 then that value gets a number of 1 (for being true). Then these 1’s are summed up to the number of unique values. But why is the range specified twice for the frequency formula? My guess is that it is there for first defining which numbers to look for in the set (whose frequency you want to find out) and the second one is to define the set in which you want to find how often each of the first set values occurs in. hiihaa, well it works so lets leave it at that..
Random experiments in software engineering 