A formula is a mathematical relationship or rule expressed in symbols that calculates the value of a cell.
A function is a pre-defined formula available in Excel that is ready to use.
A formula in excel starts with '=' inside a cell.
For example, to show the addition of B2 and B3 value in B4, we have written '=B3+B2' in B4 and hit Enter key. This gives the calculated value to B4.
The result will be shown like this.
If we try to add two string value instead of two integer values, we will get error like shown in the image below
Excel understand the operators in formula as it is treated in normal mathematical calculations. Look at the image below.
In B5, we wrote '=B2+B3*2' and this resulted as 96 because first it multiplied 8 by 2 and then added to 80.
In B5, we wrote '=(C2+C3)*2' and this resulted as 176 because first it added C2 and C3 and then multiplied by 2.
Similarly, the precedene of division, additions and substraction work.
Notice the data in B6 and C6 in above picture; normally when we copy-paste these strings in any cells, Excel tries to evaulate the values of those cells. But what if you just want to display them as it is? In that case, we need to prefix the formula or mathematical expression with single quote (') like displayed in the picture below.
The result will be like
Let's assume a scenario as shown below where we want to copy the formula of D2 to D3, D4 so that we get total of all the records.
There are two ways to copy the formula
The result would be like this
It is very easy, just select all the cells you want to know the sum & average of and look at the right side of MS Excel status bar and you would be seeing them like picture below.
Range of cells is the selection of all the cells between starting and ending of the cell. To select range of cells, we separate two cells using colon (:).
If we want to select B2 to B4, we can write B2:B4. This will select B2, B3 & B4 cells. Generally, range is used within the inbuilt functions of MS Excel.
To insert an inbuilt function in MS Excel, select a cell and then press Fx icon as shown in the picture below (In this case, function will be inserted into D5 cell).
A dialog box opens with the list of functions available in MS Excel and by default 'Most Recently Used' function apears in the list. We can change the categroy dropdown to choose a function available in that category.
For this demo, let's use AVERAGE function from the list (In case this function is not available in the default list of your screen, simply use the Search for a function box to search it).
After selecting AVERAGE from the list of functions, click OK. This opens another dialog box that ask us the argument to pass into this function.
Average baseically returns the average of its argument. So either we can write all cells one by one in Number1, Number2 etc. boxes or we can specify them by selecting the first cell (D2) and then start dragging (the screen changes as below). Keep dragging till you reach to your target cell (D4). Excel automatically surrounds selected cells with dashed border to notify that those cells have been selected.
We will get the range of cells written in Number1 text box as displayed in below picture. The result also appears on the dialog box. Now click OK.
and here is the result.
Views: 5176 | Post Order: 3