Solving Excel formulas

EXCLE formula answer

=ROUND((1 RAND()*38),0)

The round function is a function used for rounding. Its usage is round (the number to be rounded, the number of decimal points to be taken). The RAND() function can generate a random number between 0 and 1. Therefore, 1 RAND()*38 can generate a random number between 1 and 39. Therefore, this formula can be used to generate a random number between 1 and 39, with 0 decimal places rounded to an integer.

=ROUND((80 RAND()*30),2)

Similarly, the formula is to generate a random number from 80 to 110, and then take 2 decimal points for the decimal point of the random number

=ROUND(($P$10-2 RAND()*4),2)

$P$10-2 RAND()*4 is to generate the fixed cell value of P10 minus 2 plus a random number between 0 and 4, and then similarly, take 2 decimal places

=ROUND((F34*F35/100),2)

F34*F35/100 is the value of cells F34 and F35 multiplied by 100, and then similarly, the value is taken to 2 decimal places

=INDEX($S$56:$S$113,F29)

INDEX is a search reference function. The formula is to find the number of rows with the value of cell F29 in the array consisting of cells in the fixed range of S56 to S113. INDEX will return the number of rows corresponding to the fixed range of S56 to S113. In the fixed range of S56 to S113, S56 is the 1st row, S57 is the 2nd row, S58 is the 3rd row, and so on...

Excel Common Function Analysis

Detailed explanation of commonly used functions in Excel (1)

1. And function SUM:

Basic method: Select the blank cells to be summed, click Insert - Function - SUM - OK - Select the rows (columns) to be summed - OK.

Extended application: Select the "and" just now, point the mouse arrow to the lower right corner of "and", after the mouse turns into a solid " ", hold down the left button of the mouse, you can drag out the "" of the entire row (column) and".

2.Average functionAVERAGE:

Basic method: Select the blank cells to be averaged, click Insert - Function - AVERAGE - OK - Select the rows (columns) to be summed - OK.

Extended application: Select the "Average" just now, point the mouse arrow to the lower right corner of "Average", after the mouse changes to a solid " ", hold down the left button of the mouse to drag out the entire row (column) "average".

3. Insert the current date NOW:

Basic method: Select the cell where the current date will be inserted, click Insert-Function-NOW-OK-OK.

4. Sort:

Basic method: Select the content to be sorted (can include text) - Data - Sort - OK - Select the sorting conditions and ascending (descending) order - OK

5.Rank RANK:

Basic method: Select the blank cell to be ranked, click Insert - Function - Select "Statistics" in the category - RANK - OK - Select the value to be sorted (single value) in "number" )——Select all sorting values ​​in “ref”, row (column)——OK.

Extended application: Ranking in a row of "columns" - select the ranking just now - add two "$" to the formula "=RANK(I3,I3:I47)" in the formula bar just now to become "= RANK(I3,I$3:I$47)"——Select the previous ranking again, point the mouse to the lower right corner, when the mouse pointer changes to " ", hold down the left button and drag the mouse.

6. Filter:

Select the cells to be filtered - data - filter - automatic filter - a drop-down arrow will appear - after clicking the drop-down arrow, several options will appear (ascending order, descending order, custom, etc.) ——Choose customization as needed—Enter “” conditions—OK.

7. Count the total number of people COUNT:

Basic method: Select the blank cell where the total number of people will be counted, click Insert - Function - COUNT - OK - Select the row (column) to be counted - OK.

8.Conditional formatting:

Select the cells to be selected—Format—Conditional Formatting—Enter conditions—Click "Format" to select a color—OK.

9. Maximum and minimum values ​​MAX and MIN:

Basic method: Select the blank cell where the maximum value (minimum value) is to be obtained, click Insert - Function - MAX (MIN) - OK - Select the row (column) where the maximum value (minimum value) is to be obtained - Sure.

10. Fractional segment COUNTIF:

Basic method: Example: Number of people under 60 points. Select the blank cell that will be divided into segments, click Insert - Function - COUNTIF - OK - Select the row (column) that will be divided into segments - Enter the criteria (

Extended application: Example: ≥70 points and =70) - OK - in the formula bar (excluding "[]") [=COUNTIF(I5:I48,">=70＂)] After that, enter the "-" minus sign, and then enter [COUNTIF(I5:I48,''>=80'')]. The actual overall formula is: [=COUNTIF(I5:I48,''>=70'')-COUNTIF(I5:I48 ,''>=80'')】——Press Enter.

The above is the detailed content of Solving Excel formulas. For more information, please follow other related articles on the PHP Chinese website!