SLNC logo
SPECIAL LEARNING NEEDS CONSULTANT
...Making learning and technology easy, accessible, affordable and available for everyone


ARTICLE PAGE

EXCEL FORMULA AND FUNCTIONS


Written by Olufemi S.A. Bayode on 8 November 2023 @ 10:12:55 with comments

Excel

Formula and function are critical to generating result in the Microsoft Office Excel. They are essential component of the spread sheet programme because they facilitate calculation and analysis of data. An Excel spread sheet wouldn't have been more than a table with junk of text if not for its ability to perform fundamental operation as per data analysis. However, the two terms are often used interchangeably but differs in few ways.


A formula is a mathematical expression that makes calculations based on the data in the Excel worksheet. Every formula is entered into a cell wherein you want the result to appear, and usually begins with the equals sign followed by the cell coordinates including operational signs for the calculation. Once you press the ENTER key after typing the formula into the cell, the calculation is executed and corresponding result is displayed in the cell immediately.


Function is suggested to be more efficient way of performing calculation than formula in the Microsoft Office Excel. It is a built-in equations and mathematical expression for performing series of operations from simple to complex. It helps to speed things up with precision. For example, a worksheet with a data for the sale of ink in a shop for a week in column B may be added together to generate total sale for the week using a formula =b2+b3+b4+b5+b6+b7+b8+b9+b10 but you may avoid the rigour of typing this long formula by using an Excel function =sum(b2:b8) which will give you same result.


It is expedient for you to input formula in the correct syntax form for them to work. Also, you need to be mindful of your reference as well as cell to input your formula to derive desirable result. It's worthy of note that Excel performs its calculations from left to right and multiplication is performed before addition if both operators are present in the formula e.g. =30+4*5 will give you 50 and not 170. Also, division is considered before subtraction. However, you may control the order of operations by the use of the parentheses. Need to say, the distinct parts of the formula must be enclosed within the LEFT and RIGHT PARENTHESIS to get the desired result e.g. if you want the addition or the subtraction to be performed before the multiplication or division, using the earlier example: =(30+4)*5 will give you 170 because the addition was performed before the multiplication.


The standard practice for calculation using formula or function is to begin with the equals as the rest parts of the structure consist Excel function/formula and argument/operand. Some may require criteria. Therefore, such will be structured as: =Excel function(argument,”criteria”). Besides manually entering function into the cell for calculation, you can use the Insert Function menu. Here, you can obtain and use full list of Excel functions and operators.

  • Move to a blank cell close to the set of data you want Excel to act upon. For example, if your data fills a1 thru a10, then focus on a11.
  • Press the keystroke SHIFT+F3 for insert function dialogue.
  • Type what you want to do in the edit field e.g. average, addition, middle number, etc.
  • Press the ENTER key.
  • Choose your preferred function in the displayed list of result with the arrow keys and activate with the ENTER key.
  • Navigate to the Ok button with SHIFT+TAB key hen activate with the ENTER key.
  • Add the parenthesis and cell reference.
  • Press the ENTER key.


Note:
Excel argument is everything you want Excel to act upon and this is usually enclosed within a parenthesis.
The blank cell where the result will appear must be below the set of data if they are in a column as shown above while that of row must be adjacent to it.
You may combine two or more functions together to perform an operation, same as a well written and articulated formula. Also, you may combine both formula and function to perform calculation.
The press of the equals key evokes the mode expecting the typing of the formula or function. So, avoid it if you are not typing any.
You can copy the result of the calculation and paste it in other cells or fill the formula into them to generate results.
You may also type the particular function you wish to use while you highlight the parts of data you want to act upon:

  1. Go to the blank cell where you want to have your result as explained above.
  2. Press the EQUALS key and then type the name of the function you want to use follow by left parenthesis. For example, =sum(.
  3. Press the arrow key once to move to the nearest data in the range to be calculated e.g. press UP ARROW KEY once to move from a11 to a10.
  4. Highlight the cells you want to act upon. For example, press the END key, then press SHIFT+UP ARROW key to highlight the entire data being a1 thru a10.
  5. Add the right parenthesis and press the ENTER key.
  6. Return to the cell to read the result. Then, copy and paste in other cells or fill it into other cells with the appropriate keystrokes to have results as well.


There is so much to learn about the Microsoft Office Excel, but our self training guide is exactly what you need to know everything, understand and use this data analytic tool effffectively and productively. Get yourself a copy at our store to step up your skill.
Comment, Like, Tweet and Share!


Time Uploaded: 8 November 2023 @ 10:12:55

Author: Olufemi S.A. Bayode