Formulas of excel. please tell all
Answers
Answer:
Explanation:
Cell references
In the examples above, values are "hardcoded". That means results won't change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet.
Instead, use cell references so values can be changed at any time. In the screen below, C1 contains the following formula:
=A1+A2+A3 // returns 9
Formula with cell references
Notice because we are using cell references for A1, A2, and A3, these values can be changed at any time and C1 will still show an accurate result.
All formulas return a result
All formulas in Excel return a result, even when the result is an error. Below a formula is used to calculate percent change. The formula returns a correct result in D2 and D3, but returns a #DIV/0! error in D4, because B4 is empty:
Formula result can be an error
There are different ways of handling errors. In this case, you could provide the missing value in B4, or "catch" the error with the IFERROR function and display a more friendly message (or nothing at all).
Copy and paste formulas
The beauty of cell references is that they automatically update when a formula is copied to a new location. This means you don't need to enter the same basic formula again and again. In the screen below, the formula in E1 has been copied to the clipboard with Control + C:
Formula in E1 copied to clipboard
Below: formula pasted to cell E2 with Control + V. Notice cell references have changed:
Formula in E1 pasted to E2
Same formula pasted to E3. Cell addresses are updated again:
Formula in E1 pasted to E3
Relative and absolute references
The cell references above are called relative references. This means the reference is relative to the cell it lives in. The formula in E1 above is:
=B1+C1+D1 // formula in E1
Literally, this means "cell 3 columns left "+ "cell 2 columns left" + "cell 1 column left". That's why, when the formula is copied down to cell E2, it continues to work in the same way.
Relative references are extremely useful, but there are times when you don't want a cell reference to change. A cell reference that won't change when copied is called an absolute reference. To make a reference absolute, use the dollar symbol ($):
=A1 // relative reference
=$A$1 // absolute reference
For example, in the screen below, we want to multiply each value in column D by 10, which is entered in A1. By using an absolute reference for A1, we "lock" that reference so it won't change when the formula is copied to E2 and E3:
Absolute reference example
Here are the final formulas in E1, E2, and E3:
=D1*$A$1 // formula in E1
=D2*$A$1 // formula in E2
=D3*$A$1 // formula in E3
Notice the reference to D1 updates when the formula is copied, but the reference to A1 never changes. Now we can easily change the value in A1, and all three formulas recalculate. Below the value in A1 has changed from 10 to 12:
Absolute reference example after value in A1 is changed
This simple example also shows why it doesn't make sense to hardcode values into a formula. By storing the value in A1 in one place, and referring to A1 with an absolute reference, the value can be changed at any time and all associated formulas will update instantly.
Tip: you can toggle between relative and absolute syntax with the F4 key.
How to enter a formula
To enter a formula:
Select a cell
Enter an equals sign (=)
Type the formula, and press enter.
Instead of typing cell references, you can point and click, as seen below. Note references are color-coded:
Entering a formula with point and click references
All formulas in Excel must begin with an equals sign (=). No equals sign, no formula:
Forgot to enter an equals sign means no formula, just text
How to change a formula
To edit a formula, you have 3 options:
Select the cell, edit in the formula bar
Double-click the cell, edit directly
Select the cell, press F2, edit directly
No matter which option you use, press Enter to confirm changes when done. If you want to cancel, and leave the formula unchanged, click the Escape key.
Answer:
it's the valid answer
I hope it will help you
