Formulas Used In MS-Excel

 

Formulas Used In Microsoft-Excel

• Excel formulas make calculating numbers and making sense of large amounts of data simple. By knowing a few key formulas, you can do a variety of actions in Excel that will increase your productivity and decrease the risk of making calculation mistakes. 
• There are a lot of complicated formulas out there, but a great formula doesn’t have to be complex. In fact, some of the simplest formulas are the most useful and will help you maximize the capabilities of Excel.
• Here are Some important and easy to use  Excel formulas you must know, including some simple formulas that don’t have anything to with data analysis.

There are two basic ways to perform calculations in Excel : Formulas and Functions.

Formulas :

In Excel, a formula is an expression that operates on values in a range of cells or a cell.

 For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.

 

Functions:

Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names.

 For example: =SUM(A1:A3). The function sums all the values from A1 to A3.

Here are some regularly used and basic formulas to use in MS-Excel.

Sum :

• The SUM function is the singularly most used function within Excel. It is used to total values in your worksheets. These values may be continuous, non-continuous, from different worksheets, etc, or a variety thereof.
• The syntax is =SUM(number1,[number2],[...])
• An example of the formula is =SUM(A1:A4). The English translation is add up all of the values found in the range of between A1 and A4, inclusive, and displays the result.

 

Notice that I have one extra line within my formula. I do that on all of my formulas as a best practice. If I need to add any additional rows, by doing so above the blank row, I am ensured my formula will properly be modified automatically.

ROUND:

·        The ROUND function rounds a number to a specified number of digits. This should not be confused with formatting to a specified decimal places.
·   The syntax is =ROUND(number, num_digits)
·   Expanding our previous SUM formula from above, the formula is =ROUND(SUM(A1:A4),2). The English translation is add up all of the values found in the range of between A1 and A4, inclusive, round the result to two decimal places, and display the result .
·     It is important not to confuse rounding to a specific number of decimals and formatting your cell to a specific number of decimals. For example, if cell A5 below contains 18.44978. If we were to format the cell  to two decimal places, 18.45 will be displayed. However, Excel still sees it as 18.44978 (Before picture).
·    If I want Excel to see, and use in subsequent calculations, 18.45 I would need to have the following rounding formula in A5: =ROUND(SUM(A1:A4),2) (After picture)

 

COUNT:

•   The COUNT function counts the number of cells that contain numbers and counts numbers within the list of arguments.
•   The syntax is COUNT( value1, value2, …)
• Continuing on with our SUM formula from above, let’s not only add up the values of the range A1:A4, but  let’s count how many numbers are included within the range, i.e. how many cells within the range has a value in it.
•    The formula is =COUNT(A1:A4). The English translation is count how many cells within the range has a value in it and display the result.

 

SUM, A1:A4, which includes four rows. The value returned in cell A7 is three, because only three of the four rows have values in them.

·     If you are trying to count text, use the COUNTA formula which counts the non  blank cells.

 

 

IF

•  The formula makes a statement/question, if the answer is true then one response is obtained. If the answer if false, then another answer is obtained.       
• The syntax is =IF (logical_test,value_if_true,value_if_false)     
•  Continuing on with our SUM formula from above, let’s add some verbage to emphasize whether the result is greater or less than twenty.
•  The formula is =if(A5<20,”Amount is less than twenty”, ”Amount is more than twenty”). The English translation is if the value found in A5 is less than twenty THEN display the comment ‘Amount is less than twenty’ ELSE display the comment ‘Amount is more than twenty’.

ex

 

 

Average

The average function is categorized under excel statistical functions. It is used to calculate the arithmetic mean of a given set of arguments in Excel. This guide will

Show you, step-by-step, how to calculate the average in Excel.

 ex.

 

Trim :

The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside adding duplicated data in your spreadsheet.

=TRIM(text)

Ex

TRIM(A2)-Removes empty spaces in the value in cell A2.

 

MAX & MIN :

The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.

MIN :

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

MAX :

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

 CONCATENATE :

=CONCATENATE is a useful formula that takes values from multiple cells and combines them into the same cell. This formula saves time and frustration when you need to combine information in multiple cells into one cell. Instead of doing manually.=CONCATENATE can do it, in half the time and half the clicks.

 In this example, Column A has first names and Column B has last names. Using =CONCATENATE(SELECT CELL, SELECT CELL) those cells can easily be combined, which is reflected in Column D, where the full names are now in the same cell. Think of all the copying/pasting this formula just saved!

Today :

You might find yourself needing to time stamp a spreadsheet each time it is viewed.

=TODAY()

Instead of typing in the date manually, use =TODAY(). That’s right – you don’t even have to put a value in the parenthesis, and each time the spreadsheet is opened it will update with the current date.

Proper :

Excel isn’t just for data analysis; it is also a good platform for organizing and sorting information. When typing large amounts of text into Excel, =PROPER is a great formula to have in your pocket because it converts a cell of text to proper case, where the first letter of each word is capitalized, and the rest of the letters are lowercase.

In the example, quite a few names in Column A are not capitalized. Instead of clicking in each cell, deleting the first letter of each name and typing in a capitalized letter (which is a lot of extra clicks and time), =PROPER does it instantly, as you can see in Column D.

Use =Proper(SELECT A CELL) for this formula.

Even or Odd :

If you’re working with data that has a lot of decimals, this formula comes in handy. =EVEN rounds a number up to the nearest even number, and =ODD rounds a number up to the nearest odd number. If you’re working with negative numbers, these formulas still work, rounding down to the nearest even or odd number.

In this example, Column D is using the =EVEN formula and column E is using the =ODD formula.

Use =EVEN(SELECT A CELL) and =ODD(SELECT A CELL) for this formula.

Time : 

The TIME() function converts hours, minutes and seconds given as numbers to an Excel serial number, formatted with a time format.

 

If you think these formulas are makes your work lengthy and take more time then you following simple insertion that you get actually on the top bar of your excel just know about this.

Simple insertion:

Here is the simple process to than above formulas you can use this insertions to make your work more productive.

·     Typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function.

·     Excel is quite intelligent in that when you start typing the name of the function, a pop-up function hint will show. It’s from this list you’ll select your preference. However, don’t press the Enter key. Instead, press the Tab key so that you can continue to insert other options. Otherwise, you may find yourself with an invalid name error, often as ‘#NAME?’. To fix it, just re-select the cell, and go to the formula bar to complete your function.

Using Insert Function Option from Formulas Tab :

 

If you want full control of your functions insertion, using the Excel Insert Function dialogue box is all you ever need. To achieve this, go to the Formulas tab and select the first menu label Insert Function. The dialogue box will contain all the functions you need to complete your final analysis.

Using AutoSum Option :

For quick and everyday tasks, the AutoSum function is your go-to option. So, navigate to the Home tab, in the far-right corner, and click the AutoSum option. Then click the caret to show other hidden formulas. This option is also available in the Formulas tab first option after the Insert Function option. 

 Excel is really powerful spread sheet application for data analysis and reporting. After reading this article ,you would have learned the important Excel formulas and functions that will help you perform your tasks better and faster. We looked at numeric, text, data-time, and advanced Excel formulas and functions. Needless to say, Excel knowledge goes a long way in shaping many careers.

•      Share if you like it .
•     Comment your thoughts.
•     Follow for more technological content.

                Thanks For Reading !!!