Please explain the meaning of an Excel formula

Please tell me the meaning of excel formula

Experts have said a lot, so let me add a few words.

The cells involved in this formula include G19, F19, and H19

G19 and F19 are conditional judgment cells, and H19 displays the result.

In order to calculate the value of G19/F19, we need to follow certain conditions to avoid errors. The purpose of this is to ensure that the final result is accurate.

In order to help players who have not passed the level yet, let us learn about the specific puzzle solving methods. Use two if nested functions to implement: if G19 is equal to 0, display the null value ""; if G19 is not equal to 0, calculate the value of G19 divided by F19. Such operations can help players solve puzzles better. Hope this tip helps you!

If F19 is equal to 0 or is a non-numeric value, then G19/F19 will generate an error value. At this time, we should display a null value ""; and if F19 is not equal to 0 and is not a non-numeric value, then we can execute the corresponding function .

IF(G19=0,"＂",G19/F19)

Because in division, 0 cannot be used as the divisor, and the same is true in functions. If the divisor is 0 or a non-numeric value, an error will be displayed.

So ISERROR(G19/F19) is used to prevent F19=0 or non-numeric value from occurring. It ignores error values.

Who can help me interpret the meaning of EXCEL formula? Thank you!

This formula calculates the number of cells with unique content in a column or row of cells.

The following is the disassembly process:

1. IF(LEN(A3:A100)>0,MATCH(A3:A100,A3:A100,0),＂＂)

This formula calculates one by one whether the content length of each cell in A3:A100 is greater than 0. If it is greater than 0, then search for this value in A3:A100 and add the first found cell with the same result. The grid position is returned. The calculation result is an array, the size of the array is the same as A3:A100, that is, 98 numbers. Because the Match function here will only return the address found first, and the rest will not change later, such as:

A1

1

A2

1

A3

2

A4

2

A5

3

Then the result of running IF(LEN(A3:A5)>0,MATCH(A3:A5,A3:A5,0),"") will be an array. This array is: {1,1,3, 3,5}

2. FREQUENCY(IF(LEN(A3:A10)>0,MATCH(A3:A10,A3:A10,0),''), IF(LEN(A3:A10)>0,MATCH(A3: A10,A3:A10,0),"＂"))

This formula is equivalent to Frequency (the result of the first step of calculation, the result of the first step of calculation). Frequency(a,b) calculates the number of times a appears in range b. If it appears multiple times, the total number of occurrences of the value will be returned when it appears for the first time, and 0 will be returned for the second and subsequent occurrences.

Continue with the above example of A3:A5, Frequency(IF(LEN(A3:A5)>0,MATCH(A3:A5,A3:A5,0),＂＂),IF(LEN(A3:A5) >0,MATCH(A3:A5,A3:A5,0),""))) is also an array. This is to calculate the number of occurrences of each number in the array {1,1,3,3,5}. The calculation result is: {2,0,2,0,1}

3. IF(FREQUENCY(……)>0,1)

This formula is the simplest array usage of the if formula. It compares each value calculated in the second step with 0. If >0, it returns 1, otherwise, returns 0. The calculation result is also an array.

Continue with the example of A3:A5. If(Frequency(IF(LEN(A3:A5)>0,MATCH(A3:A5,A3:A5,0),"＂),IF(LEN(A3:A5)>0,MATCH(A3:A5,A3 :A5,0),""))>0,1) can be simplified to: if({2,0,2,0,1}>0,1), the calculation result is {1,0,1,0 ,1}

4. sum(……)

Add the values ​​in the above array.

Taking the above example, the calculation result of Sum({1,0,1,0,1}) is: 1 0 1 0 1=3

To sum up:

The final formula is to calculate the number of cells with non-duplicate content in the given vertical example.

Can count a single row or column, but if there are different numbers of blank symbols or other special symbols (such as carriage returns) in a cell, it will be counted. If cell A1 has two spaces and cell A2 has three spaces, count A1 and A2 separately, twice. Therefore, be sure to remove blank symbols and other special symbols (such as carriage return) before use.

Another formula similar to this formula is: Sumproduct(1/Countif(A3:A100,A3:A100))

The above, I hope it will be helpful to you. If you think my explanation is okay, please add some points to encourage me! ^_^

The above is the detailed content of Please explain the meaning of an Excel formula. For more information, please follow other related articles on the PHP Chinese website!