MS Excel Formulas and Functions

Interesting Facts

  • In an Excel sheet the earliest date allowed for calculations is 1st January, 1900.
  • Type this formula in excel cell (or) formula bar: =1*(0.5-0.4-0.1) you will get a shocking results. Try yourself.



            In this post here I m mentioning some of the basic formulas and functions which will be very helpful while working in Excel. There exist more than 300+ functions in excel and the number is growing by version to version. You can find all available Excel functions in Function Library on Formula Tab



Some Excel basic Functions you should definitely know

        Lets know about the basic arithmetic operations in worksheet. Important thing to be known is you should use (=) before using any formula or function.

SUM

   It is a basic formula to know in Excel, It allows you to add up numbers in different ways. Let me proceed with examples


  •     =SUM(C6+C7+C8+C9+C10) adds up values from cells C6 to C10.
  •     =SUM(C6:C9) adds up values in cells C6 through C9.
  •     =SUM(C6,C8) adds up values in cells C6 and C8.


AVERAGE

    Using this function Average you can perform the basic arithmetic mean of numbers. Here are some of the examples.



  •    =AVERAGE(C6,C7,C8,C9,C10) calculates the average values from C6 to C10.
  •    =AVERAGE(C6:C9) does the same function as mentioned above

MAX & MIN

    Using this function in worksheet with lots of numbers you can immediately find the largest number using =MAX function, similarly the smallest number using =MIN function. This function is explained in the image below.



If

    With this function, the worksheet will tell you whether a certain condition is met or not. Using this =IF formula you can get to know whether the condition is Yes or No. This function is explained in the below image.


TRIM

    If you copy and paste data into a worksheet there is a chance that the pasted data may have extra spaces or hidden characters and even it can mess up the formula. Using =TRIM function the pasted data will not cause any extra space and there will be no errors in formula.



CONCATENATE

    It is a useful formula that takes values from multiple cells and combine them into the same cell. This formula saves much time when you need to combine information's in multiple cells into one cell. This is explained in the below image.


LEN

   The LEN function is is used to know the number of characters in a certain cell. Important thing in LEN Function is, it counts all characters including spaces. You can find the example in the below image.


AND & OR

    The AND & OR are the two most popular logical functions to check the multiple criteria. AND Function returns True if all conditions are met, if not it I'll be False. OR Function returns True if any condition is met, if not it I'll be False. You can find the example in the below image which helps you to understand how it works.


COUNT & COUNTA

    COUNT Function is used to know how many cells are given in a range containing numeric values where as COUNTA Function counts all cells that are not blank (i.e.,) it counts all cells containing numbers, text, dates etc. You can find the example in the below image.


    These are some of the MS Excel formulas and functions which is used quite often while working. I hope this blog was helpful for the users.