How to interpret excel formulas

How to interpret excel formulas

1. Read more function help. The help for each function contains the basic usage of the function and some "key points", as well as requirements for data sorting, reference types, etc. Of course, function help does not cover all the subtleties of functions, otherwise, there would not be so many posts asking for "explanations".

2. The parameters of the function are separated by commas. (Don’t be ridiculous, this is the most basic basic skill. A single function is nothing. The formula that combines multiple functions depends on it.) These commas are the "cow" joints. First, break the long formula into eight pieces and then look at them one by one. Once you understand it, it will be much easier to put it together and read.

3. Dugu Nine Swords - Just kidding, here is the homophonic "F9 key". The F9 key is used to "smear" formulas, which has a very strong effect on interpretation, especially array formulas. However, if the data area contained in the formula is too large (such as hundreds of lines), you can change the area. Specific method: For example, the following simple array formula

=sum(if(A1:A3amp; gt;0,B1:B3)), use the mouse to "black out the A1:A3>0 part" in the editing bar ", press the F9 key, and you will see {True; True; False} (assuming A3 is not satisfied), which means that the condition of if is an array of logical values ??with 3 rows and 1 column. ——Don’t forget to press ESC to cancel after reading, otherwise the formula will change.

4. Formula review - Tools → Formula review → Formula evaluation. The magnifying glass with fx is basically the same as F9. It can see the results of formula operation step by step (but both have certain effects. Limitations, the specific situation is not yet clear, fx sometimes causes Excel to restart). Use it together.

5. Pay attention to the definition name: Many people use the definition name to make the formula concise and intuitive, making it easier for "Pao Ding" to solve it. Press Ctrl F3 to view the definition name (or menu insertion) Name) Definition), if the name is a reference to a cell range, when you click "Reference Location" below the name box, a dotted selection box will appear in the corresponding area. You can also select a blank area, press the F3 key, and select "Paste List" to copy the defined name to the blank area. ;