Excel Formulas(Quick Ways to access excel formulas)

Quick-ways-to-access-Excel-Formulas

In today’s fast moving world it is very important to know Microsoft Excel Formulas because it helps to maintain records of any organization.

Before looking into formulas let’s first go through its advantages.

Advantages of using Microsoft Excel Formulas

  1. Microsoft Excel offers users hundreds of different functions for a variety of purposes but not everyone is aware about its usage
  2. In addition to that it can analyze personal finance or any large data set in very simple way.
  3. Through Excel Formulas,complex calculations becomes very easy.
  4. Excel cell reference allows user to use the same formula for different values.
  5. Above all excel helps in data analysis and boosting up productivity in any process.
  • Almost everyone is aware about its basic working but very few are proficient with excel formulas.
  • Therefore,it is the need of an hour to learn formulas in order to save good amount of time.
  • However finding the right function for your data set can be very tricky.
  • Knowing Excel makes a person more marketable.

Apart from learning excel tips and tricks,it is very important for a user to be updated with every latest technology.Additionally,smarter ways to complete your work effectively.

During daily official work a person probably face problems while sending a video on whatspp or email.For that you can just refer how to compress video and send it easily.

This is finally the right place for people who have been struggling to find the appropriate formulas.Here is a list of quick ways to access Microsoft Excel Formulas and Functions.

Above all please refer the below data which is used in all listed formulas while doing calculations.

Top Products Product Code Quantity Price
Mobile A 4 5000
Camera B 3 2000
Laptop C 2 1500
Watches W 5 3000
TV T 6 4000
Refrigerator R 8 9000

SUM

First of all SUM() function is used for adding the range of cells

=SUM(start range:end range)   

Excel Formulas - SUM FORMULA
SUM FORMULA

MIN

MIN() function is used to find minimum number between two or more numbers/from a range of numbers.

=MIN(start range:end range) or =MIN(Number 1,Number 2)

Excel Formulas - MIN FORMULA-1
MIN FORMULA-1

MIN FORMULA-2
MIN FORMULA-2

MAX

Similarly,MAX() function is used to find maximum number between two or more numbers/from a range of numbers.

=MAX(start range:end range) or =MAX(Number 1,Number 2)

TODAY

TODAY() function returns current date as described in settings.

=TODAY()

AVERAGE

AVERAGE() function returns the average (arithmetic mean) of the arguments.

=AVERAGE( start range:end range)

Excel Formulas - AVERAGE FORMULA
AVERAGE FORMULA

In the above screenshot as you can see when average is calculated by =SUM/COUNT result is same as the result obtained from AVERAGE FORMULA which was obviously one step quick method. Hence this formula is an example that how easy it becomes if we are familiar with appropriate formula.

CONCATENATE

CONCATENATE() function is used to join several text strings into one text string.

= CONCATENATE(text1, [text2], ….)

Excel Formulas - CONCATENATE FORMULA
CONCATENATE FORMULA

CONVERT

CONVERT() function converts a number from one measurement system to another

= CONVERT(number,from_unit,to_unit)

Excel Formulas - CONVERT
CONVERT FORMULA

LEN

LEN() function returns the number of characters in a text string

= LEN(text)

Excel Formulas - LEN FORMULA
LEN FORMULA

IF

IF() function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

= IF(logical_test,[value_if_true],[value_if_false])

Excel Formulas - IF FUNCTION
IF FUNCTION

SUMPRODUCT

SUMPRODUCT() function returns the sum of the products of corresponding ranges or arrays.It first multiples then adds the values of the input arrays.

= SUMPRODUCT(array1,[array2],[array3],….)

Note :

It saves lots of time and very useful formula for big calculations.Rather you need to follow a lengthy process first calculate the product of arrays for each cell and then add input arrays using SUM function.Instead use only one formula SUMPRODUCT().

Excel Formulas - SUM PRODUCT FORMULA
SUM PRODUCT FORMULA

LEFT & RIGHT

LEFT() function returns the specified number of characters from the start of a text string.

RIGHT() function returns the specified number of characters from the end of a text string.

= LEFT(text,[num_chars])

= RIGHT(text,[num_chars])

Excel Formulas - LEFT & RIGHT FUNCTION
LEFT & RIGHT FUNCTION

MID

MID() function returns the characters from the middle of a text string,given a starting position and length.

= MID(text,start_num,[num_chars])

Excel Formulas - MID FUNCTION
MID FUNCTION

SUBSTITUTE

SUBSTITUTE() function replaces existing text with new text in a text string

= SUBSTITUTE(text,old_text,new_text,[instance_num])

  • text -text for which you want to substitute characters.(Required)
  • old_text -text you want to replace.(Required)
  • new_text -text you want to replace old_text with.(Required)
  • instance_num -Specifies which occurrence of old_text you want to replace with new_text.(Optional)
SUBSTITUTE FUNCTION
SUBSTITUTE FUNCTION

FIND

FIND() function returns the starting position of one text string within another text string.FIND is case-sensitive.

= FIND(find_text,within_text,[start_num])

Note : start_num – Specifies the character at which to start the search.The first character in within_text is character number 1. If you omit start_num,it is assumed to be 1.

FIND FUNCTION
FIND FUNCTION

EXACT

EXACT() function checks whether two strings are exactly same and returns true or false.EXACT is case-sensitive.

= EXACT(text1,text2)

EXACT FUNCTION
EXACT FUNCTION

SUMIF

SUMIF() function add the cells specified by a given condition or criteria.

= SUMIF(range,criteria,[sum_range])

  • range -range of cells you want to be evaluated by criteria.(Required)
  • criteria – criteria in the form of a number,text,a cell reference,expression or a function that defines which cells will be added.(Required)
  • sum_range – the cells to add together.If sum_range is omitted,the cells in range are added together instead(Optional)
SUM IF FUNCTION
SUM IF FUNCTION

AVERAGEIF

AVERAGEIF() function finds average(arithmetic mean) for the cells specified by a given condition or criteria.

= AVERAGEIF(range,criteria,[average_range])

  • range – one or more cells to average,including numbers or names,arrays,or references that contain numbers.(Required)
  • criteria – criteria in the form of a number,text,a cell reference or expression that defines which cells are averaged.(Required)
  • average_range – the actual set of cells to average.If average_range is omitted,range is used.(Optional)
AVERAGE IF FUNCTION
AVERAGE IF FUNCTION

VLOOKUP

VLOOKUP() function looks for a value in the leftmost column of a table,and then returns a value in the same row from a column you specify.By default,the table must be sorted in ascending order.

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

  • lookup_value – select the value to lookup
  • table_array – range containing the lookup value.
  • col_index_num – the column number in the range containing the return value
  • range_lookup- optionally specify TRUE for approximate match or FALSE for an exact match.
 VLOOKUP FUNCTION
VLOOKUP FUNCTION

LOWER & UPPER

LOWER() function converts all letters in a text string to lowercase.

In the end,UPPER() function converts a text string to all uppercase letters.

= LOWER(text)

= UPPER(text)

LOWER & UPPER FUNCTION
LOWER & UPPER FUNCTION

Above all the formulas will help a user in day-to-day business.I have listed mostly the formulas which will be more useful for regular work.

5 comments on “Excel Formulas(Quick Ways to access excel formulas)

Leave a Reply

Your email address will not be published. Required fields are marked *