If you are relatively new to writing Excel formulas, pay attention to these concepts.
Ignoring them will lead you to unnecessary headaches...
1) Every Excel formula starts with the = sign
The = sign is automatically entered when you insert an Excel function.
A formula without the equal sign is considered a cell content.
Let's see an example...
Given the fact that the cell A1 contains the number 10 and the cell A2 contains the number 20; the formula: =A1+A2 will retrieve 30, while the formula A1+A2 will be pure content. It will be shown as A1+A2 in the given cell.
At the same time, the function =SUM(A1:A15) will retrieve the results, while this one SUM(A1:A15) not.
The = sign must be entered by hand when you write the formula directly on a cell.
Important reminder: a formula entered on a cell that is formatted as text will not be evaluated even if it has the = sign.
2) An Excel formula may contain numbers (constants) or references to cells
The formula: =A1+20 contains a reference and a number.
A1 has the value of the content of the cell A1, while the number 20 is always 20.
The advantage of having references instead of constants is that you can change a whole spreadsheets by changing the input cells.
Otherwise, you should edit each instance of the formula in the sheet.
3) The Excel formula may contain plain Math, other functions or a combination of them
You can write a formula this way: =(A1+A2+A3+A4)/4+10 with plain Math or this way using a function and Math: =AVERAGE(A1:A4)+10.
4) Arguments are separated by commas and should be entered according to a specific order (syntax)
The arguments of a function are separated by commas.
The order in which they are entered are unambiguous and correspond to the given function syntax. For example: the formula =SUMIF(A1:A10,">20",B1:B10) corresponds to this syntax: =SUMIF( range, criteria, sum_range )
5) The result of an Excel formula is retrieved in the same cell on which was written
After you hit enter, the formula is calculated and the result is shown in the same cell.
Important reminder: Excel won't calculate the formula result when it lacks inputs. On the other hand, you will get a result if all the arguments and logic are correctly entered.
6) You cannot see an Excel formula in a cell just its results.
You can see the contents in the formula bar, by pressing F2 or by showing formulas instead of results (CTRL + ` )
A formula shows by default its result in the cell where it is contained.
This may lead you to confusion with the inputs. An input and a formula result are not distinguishable at first sight.
7) Excel computes formulas in a well known order
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following sequence.
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first.
The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3=11
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3=21
Conclusion
These Excel formula nuggets will help you to get started on the fascinating world of Excel spreadsheets.
Ignoring them will lead you to unnecessary headaches...
1) Every Excel formula starts with the = sign
The = sign is automatically entered when you insert an Excel function.
A formula without the equal sign is considered a cell content.
Let's see an example...
Given the fact that the cell A1 contains the number 10 and the cell A2 contains the number 20; the formula: =A1+A2 will retrieve 30, while the formula A1+A2 will be pure content. It will be shown as A1+A2 in the given cell.
At the same time, the function =SUM(A1:A15) will retrieve the results, while this one SUM(A1:A15) not.
The = sign must be entered by hand when you write the formula directly on a cell.
Important reminder: a formula entered on a cell that is formatted as text will not be evaluated even if it has the = sign.
2) An Excel formula may contain numbers (constants) or references to cells
The formula: =A1+20 contains a reference and a number.
A1 has the value of the content of the cell A1, while the number 20 is always 20.
The advantage of having references instead of constants is that you can change a whole spreadsheets by changing the input cells.
Otherwise, you should edit each instance of the formula in the sheet.
3) The Excel formula may contain plain Math, other functions or a combination of them
You can write a formula this way: =(A1+A2+A3+A4)/4+10 with plain Math or this way using a function and Math: =AVERAGE(A1:A4)+10.
4) Arguments are separated by commas and should be entered according to a specific order (syntax)
The arguments of a function are separated by commas.
The order in which they are entered are unambiguous and correspond to the given function syntax. For example: the formula =SUMIF(A1:A10,">20",B1:B10) corresponds to this syntax: =SUMIF( range, criteria, sum_range )
5) The result of an Excel formula is retrieved in the same cell on which was written
After you hit enter, the formula is calculated and the result is shown in the same cell.
Important reminder: Excel won't calculate the formula result when it lacks inputs. On the other hand, you will get a result if all the arguments and logic are correctly entered.
6) You cannot see an Excel formula in a cell just its results.
You can see the contents in the formula bar, by pressing F2 or by showing formulas instead of results (CTRL + ` )
A formula shows by default its result in the cell where it is contained.
This may lead you to confusion with the inputs. An input and a formula result are not distinguishable at first sight.
7) Excel computes formulas in a well known order
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following sequence.
- - Negation (as in -1)
- % Percent
- ^ Exponentiation
- * and / Multiplication and division
- + and - Addition and subtraction
- & Connects two strings of text (concatenation)
- = < > <= >= <> Comparison
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first.
The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3=11
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3=21
Conclusion
These Excel formula nuggets will help you to get started on the fascinating world of Excel spreadsheets.
For more professional Excel tips and advice, visit http://www.excel-spreadsheet-authors.com/
Learn more about: Excel Formulas
Learn more about: Excel Formulas
Article Source: http://EzineArticles.com/?expert=John_Franco_Farias
No comments:
Post a Comment