![]() ![]() ![]() For example, this formula can produce a list of random exam results: =CHOOSE(RANDBETWEEN(1,4), 'Poor', 'Satisfactory', 'Good', 'Excellent') The formula's logic is obvious: RANDBETWEEN generates random numbers from 1 to 4 and CHOOSE returns a corresponding value from the predefined list of four values. In this function, Excel evaluates the values within the list or range, and selects the most frequently occurring number as the average value of the group. Nest it in the indexnum argument of CHOOSE, and your formula will generate almost any random data you want. Then you will immediately see how many times each number has been called. To extract the text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE. =CHOOSE((B20) + (B2=51) + (B2=101), B2.$E$2, B2.$E$3, B2.$E$4) Excel CHOOSE formula to generate random data As you probably know, Microsoft Excel has a special function to generate random integers between the bottom and top numbers that you specify -. The easiest way to do this would probably be to construct a pivot table from the data using the Telephone number as both the row field and the data field and set the data field to summarise by Count. Just remember to fix the references using the $ sign. As an example, let's calculate the commission of each seller depending on their sales: Commission Sales 5% $0 to $50 7% $51 to $100 10% over $101 With the sales amount in B2, the formula takes the following shape: =CHOOSE((B20) + (B2=51) + (B2=101), B2.5%, B2.7%, B2.10%) Instead of hardcoding the percentages in the formula, you can refer to the corresponding cell in your reference table, if there is any. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |