Hevitra ato Anatiny
The need to find the highest and lowest values in any business is obvious: the most profitable products or valuable customers, the largest shipments or shipments, and so on.
But along with this, sometimes you have to look in the data not for the top, but for the most frequently occurring values, which, although it sounds similar, is, in fact, not at all the same. In relation to a store, for example, this may be a search for the most frequently purchased goods, rather than the most profitable ones, or the most frequently occurring number of items in an order, minutes in a conversation, etc.
In such a situation, the problem will have to be solved a little differently, depending on what we are dealing with – numbers or text.
Finding the Most Common Numbers
Suppose we are faced with the task of analyzing the available data on sales in a store in order to determine the most frequently occurring number of items purchased. To determine the most frequent number in a range, you can use the function FASHION (FASHION):
That is, according to our statistics, most often buyers purchase 3 pcs. goods.
If there is not one, but several values at once that occur the same maximum number of times (several modes), then to identify them, you can use the function FASHION.NSK (MODE.MULT). It must be entered as an array formula, i.e. select several empty cells at once, so that there is enough for all modes with a margin and enter =MODA.NSK(B2:B16) into the formula bar and press the keyboard shortcut Ctrl + Shift + Enter.
At the output, we will get a list of all mods from our data:
That is, judging by our data, they often take not only 3, but also 16 pieces. goods. Please note that there are only two modes in our data (3 and 16), so the rest of the cells allocated “in reserve” will be with the #N/A error.
Frequency analysis by bands with the FREQUENCY function
If it is necessary to analyze not integers, but fractional numbers, then it would be more correct to evaluate not the number of identical values, but their falling into the specified ranges. For example, we need to understand what weight is most often purchased by the goods in order to choose the right trolleys and packaging bags of the right size for the store. In other words, we need to determine how many numbers fall into the interval 1..5 kg, how many into the interval 5..10 kg, etc.
Mba hamahana olana mitovy amin'izany dia azonao ampiasaina ny fiasa hatetika (FREQUENCY). For it, you need to prepare cells in advance with intervals (pockets) of interest to us and then select an empty range of cells (G2: G5) one cell larger than the range of pockets (F2: F4) and enter it as an array formula by pressing the combination at the end Ctrl + Shift + Enter:
Frequency analysis with a pivot table with grouping
An alternative solution to the problem: create a pivot table where you put the weight of purchases in the rows area, and the number of customers in the values area, and then apply grouping – right-click on the weight values and select the command Group (vondrona). In the window that appears, you can set the limits and the grouping step:
… and after clicking on the button OK get a table with a count of the number of hits of buyers in each grouping range:
maharatsy ny mifampiresaka Atỳ:
- the grouping step can only be constant, unlike the function hatetika, where pockets can be specified absolutely any
- the pivot table needs to be updated when the source data changes (by clicking the right mouse button – Refresh), and the function is recalculated automatically on the fly
Search for the most frequently occurring text
If we are not dealing with numbers, but with text, then the approach to the solution will be fundamentally different. Suppose we have a table with 100 rows of items sold in a store and we want to determine which items were bought most often?
The simplest and most obvious solution would be to add a column next to the function COUNTIF (COUNTIF)to count the number of occurrences of each item in column A:
Then, of course, sort the resulting column in descending order and look at the first lines.
Or add a column with ones to the original list and build a summary table based on the resulting table, counting the total number of ones for each product:
If there is not a lot of source data and you basically don’t want to use pivot tables, then you can use the array formula:
Andeha hovakivakintsika tsirairay:
- COUNTIF(A2:A20;A2:A20) is an array formula that looks in turn for the number of occurrences of each product in the range A2:A100 and produces an array with the number of repetitions at the output, i.e., in fact, replaces the additional column
- MAX – finds the largest number in the array of occurrences, i.e. most purchased product
- MATCH – calculates the ordinal number of the row in the table where MAX found the largest number
- INDEX – returns from the table the contents of the cell with the number found by MATCH
- Counting the number of unique values in a list
- Extracting Unique Items from a Duplicate List
- Mivondrona ao anaty tabilao pivot