Search On Google

Sunday, February 10, 2019

What are common MS Excel Formulas?

Common Formulas   
  
Formulas          Example                                          Description

SUM                 =SUM(E1:E18)”                          Add for range E1 to E18
COUNT           =count(E1:E18)”                          Count no of cells in range
MIN                  =MIN(E1:E18)”                            give minimum value from a range
MAX                 =MAX(E1:E18)”                           give maximum value from a range
AVERAGE       =AVERAGE(E1:E18)”                   give average value from a range
LEN                 =LEN(E1)"                                    give text length
SUMIF             =SUMIF(E1:E12,">10",E1:E4)”    perform addition based on criteria.
AVERAGEIF    =SUMIF(E1:E12,">10",E1:E4)”    perform average based on criteria.
NOW               =NOW()”                                      give current date and time
       
       
STRING FORMULA   
   
FIND             =FIND("li","online",1)                  =find(findtext, withintext, startnumber)”
REPLACE     =REPLACE("online",2,2,"of")     =replace(oldtext,startnumber,numberofchar,newtext”)”
LEFT            =LEFT("onlinetutorial",4)             return left 4 char
RIGHT         =RIGHT("onlinetutorial",4)           return right 4 char
ISTEXT        =istext(value)                               return true if value is text
MID              =MID("GURU99",5,9)                  return middle text of given range 5 to 9
       
       
DATE TIME FORMULA
       
DATE            “=DATE(2019,1,4) ”                          return date
DAYS            “=DAYS(E1:E3)”                               give no of days between given date range.
MONTH       “=MONTH("4/1/2019") ”                   return month only
MINUTES    “=MINUTE("12:31") ”                        return minutes from time value
YEAR          “=YEAR("4/1/2019") ”                        return year value
       
NUMERIC FORMULA       
       
ISNUMBER       “=ISNUMBER(E3) ”                  return true if value is numeric
ROUND            “=ROUND(3.18789,4)”             to get round off of given value
MOD                 “=MOD(10,8)”                          return remainder
RAND               “=RAND()”                               generate random number between 0 to 1
PI                      “=PI()”                                      return value 3.14 value of PI
POWER            “=POWER(5,3)”                      “=POWER(number,power)” will give 5^3=5*5*5 from example
ROMAN            “=ROMAN(2018) ”                  convert number to roman number
MEDIAN           “=MEDIAN(9,2,5,1,7) ”             calculate median of numbers as given





No comments:

Post a Comment

About Me

My photo
Mumbai, Maharashtra, India