50+ basic to advance excel function formulae and errors|technomintstudy

Pratima verma

 Microsoft Excel (latest version) 50+ basic function fumulae and errors

Function formula :-

Excel, technomintstudy


  1. Show date or time     =now( )
  2. Show only date.   =today( ) . 
  3. Count of days  =n(selected new date                                              - select old date) 
  4. Convert upper to lower case   =lower                                                  (select capital letter) 
  5. Convert to proper.    =proper (select text) 
  6. Convert lower to upper   =uper (select                                                                    text) 
  7. Count of character.      =len (select text) 
  8. Count digit cell.      =count (select digit all) 
  9. Count same number =Count if (select                                                    range,type digit) 
  10. Count of blank cell.    =Countblank (select                                             range) 
  11. Count of rows.   =rows(select range area) 
  12. Count of column =columns(select range                                       area) 
  13. Show side of row  =row ( ) 
  14. Show side of column    =column( )
  15. Show value of power     =power (2,5)
  16. Joint of two name together =conatenate (select name, space, select other name) 
  17. Show average.   =average(select marks) 
  18. For multiplication   =product(first digit,                                         second digit) 
  19. Age calculation =                                      Years :- =dated if(select date, today(  ), "y")   month :-   =dated if(select date, to day( ),                          "ym")           ;.                                 days :- =dated if(select date, today( ),                             "md")
  20. Find:- find one string of text within another string of text and return the number of the character at which find text first occur.                   =find ("&"  "jack & jill")
  21.  Left: it returns the first (or left most) character or characters in first string                                  =left ("salesman ", 5)
  22. Right := it returns the last (or right most) character in the text string.                                        =right("salesman", 3)
  23. Trim:= it removes all spaces from text except from single space between words                   =trim(select text)
  24. Abs;= it returns the absolute value of number                                                                          =abs (-162)
  25. Even:- it returns number rounded upto the nearest even integer                                                                  =even(5.5)                                                            =even (5)                                                              =even (-15).                                                          =even (6)
  26. Fact := it returns the factorial of number .                        =fact (5)
  27. Round :=it round a number to a specified number of digit.          =round(96.54,1)
  28. Sqrt:-it returns a positive square root.                           =sqrt(64)
  29. V lookup :=any record find show in details of the list.       =vlookup (select text, select                            range area, no of column ,o)
  30. Hlookup: search data.           =hlookup(select text, select range area, no of row, o)
  31. Roman function convert a number to a roman numeral         =roman (type number)
  32. Return the reminder after number is devided by divisor :.                                                         =mod(number, divisor)
  33. Mid functions extracts a substring from a string (starting of any position).         =mid(select text, start position number, number of character)
  34. Sum:.            =sum(range area)
  35. Max:=returns the largest value in the range = max(range area)
  36. Min:=returns the minimum value in the range         =min (range area) 
  37. Rept:= repeats the given list a specified number of times.        =rept("ram", 5)
  38. Date value :=the function converts a date in text from to date serial number                             =date value ("date")
  39. Day:=this function returns the day of a date                 =day("date")
  40. Month :=this function returns the month of a date.              =month("date")
  41. Year:=this function returns the year of a date.                =year("date") 
  42. Weekday: =this function returns an integer from 1 for Sunday and 7 for saturday                            =weekday ("date") 
  43. Subrtraction:. =(first value - second value) 
  44. Division :.    =(first value /second value) 
  45. Fv ( ) function : returns the future value of an investment based on periodic, constant payment and constant interest rate              =fv(interest /condition, term, installment) 
  46. Pmt ( ) function : calculates the payment for a loan based on constant payment and constant interest rate.                                          =pmt (interest/condition, term, loan amount) 
  47. Pv ( ) function :returns the present value of an investment.              =pv(interest/condition, term, loan installment)
  48. Year frac :returns fractional number of years between a start date and end date.       = yearfrac (start date, end date [basic] ) 
  49. Int function :chop off the decimal value       =int(yearfrac(b1, today () ))

Some errors and there solution in excel 




1. ####=increase column width 
2. #name=correct your formula 
3. #value! =correct the variable mentioned in formula.    
4. #div?O! =when you try to devide a number by zero 
5. #wull! = when you forget to separate the two cell refrences correctly 
6. #ref !=when your formula contains some incorrect cell references 
7.#num !=the formula or function contains invalid number value 
8. #n/a! =no value available 


 Priyanshu Verma