the number that appears most/least
the number that appears most/least
Most frequent is MODE, so =MODE(B1:B16). With conditions, you could use =AGGREGATE(13,6,B1:b16/(B1:B16>=6)/(B1:B16<=7)), but I prefer avoiding Magic Numbers for readability and maintainability, so there's also array formulas like =MODE(IF((B1:B16>=6)*(B1:B16<=7),B1:B16). Note, however, that there are 4 instances of 5.1 and 5.2, but 5.1 appears first in B1:B16, so Excel returns it as the mode.
For least occurring number, there are 5 numbers which appear only once. If you want the first of these, try =INDEX(B1:B16,MATCH(1,COUNTIF(B1:B16,B1:B16),0)). To add conditions, use COUNTIFS and add those conditions as additional arguments.
Hi,
you can use the frequency function for the most option.
regarding the least - you have multiple answers, since there are several numbers numbers that appear only once and can comply with your condition.
so what answer are you expecting to see if case of multiple results?
D3
E3:Please Login or Register to view this content.
F3Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks