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
- Microsoft Excel offers users hundreds of different functions for a variety of purposes but not everyone is aware about its usage
- In addition to that it can analyze personal finance or any large data set in very simple way.
- Through Excel Formulas,complex calculations becomes very easy.
- Excel cell reference allows user to use the same formula for different values.
- 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|
First of all SUM() function is used for adding the range of cells
=SUM(start range:end range)
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)
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() function returns current date as described in settings.
AVERAGE() function returns the average (arithmetic mean) of the arguments.
=AVERAGE( start range:end range)
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() function is used to join several text strings into one text string.
= CONCATENATE(text1, [text2], ….)
CONVERT() function converts a number from one measurement system to another
LEN() function returns the number of characters in a text string
IF() function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
SUMPRODUCT() function returns the sum of the products of corresponding ranges or arrays.It first multiples then adds the values of the input arrays.
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().
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.
MID() function returns the characters from the middle of a text string,given a starting position and length.
SUBSTITUTE() function replaces existing text with new text in a text string
- 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)
FIND() function returns the starting position of one text string within another text string.FIND is case-sensitive.
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.
EXACT() function checks whether two strings are exactly same and returns true or false.EXACT is case-sensitive.
SUMIF() function add the cells specified by a given condition or criteria.
- 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)
AVERAGEIF() function finds average(arithmetic mean) for the cells specified by a given condition or criteria.
- 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)
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.
- 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.
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.
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.