This post focuses on quick ways to access Excel formulas with step-by-step description which are very useful in our day-to-day life.
Advantages of Excel Formulas
- Performs automated math operations to data in the spreadsheet.
- Cell reference in excel allows users to use the same formula for different values.
- Calculates large quantities of data in an efficient manner.
- Gives better and faster results.
- Maintain records of any organization.
- Microsoft Excel offers users hundreds of different functions for a variety of purposes but not everyone is aware of its usage.
- In addition to that, it can also analyze personal finance.
- Helps in data analysis and boosting up productivity in any process.
- Knowing Excel makes a person more marketable.
Almost every person knows its basic working but very few are proficient with proper formulas. So it is very much important to know “how to use the right formula at the right place”.
Apart from learning excel tips and tricks, a person should know smarter ways to complete work efficiently. Additionally, it is the need of an hour to be aware of every latest technology.
In order to save problems faced while sending a large video file. Please read the below article.
Article – How to compress a video?
This is finally the right place for people who have been struggling to find appropriate formulas. Because here you will find a list of quick ways to access Microsoft Excel Formulas.
Above all please refer to the below data while doing calculations.
|Top Products||Product Code||Quantity||Price|
Add the values for a range of cells.
=SUM(start range:end range)
Finds minimum number between two or more numbers/from a range of numbers.
=MIN(start range:end range) or =MIN(Number 1,Number 2)
Similarly, this function finds maximum number between two or more numbers/from a range of numbers.
=MAX(start range:end range) or =MAX(Number 1,Number 2)
Returns current date as described in settings.
Returns the average (arithmetic mean) of the arguments.
=AVERAGE( start range:end range)
In the above screenshot as you can see when the average is calculated by =SUM/COUNT result is the same as the result obtained from the AVERAGE FORMULA but obviously result from AVERAGE Function is only a single step process. Hence this formula is an example that how easy it becomes if we are familiar with an appropriate formula.
Joins several text strings into one text string.
= CONCATENATE(text1, [text2], ….)
It converts a number from one measurement system to another
Returns the number of characters in a text string.
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
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 a very useful formula for big calculations as it follows very few steps rather than other formulas which follows a lengthy process. Like, Firstly calculate the product of arrays for each cell and then add input arrays using the SUM function. Instead of that 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.
Returns the characters from the middle of a text string,given a starting position and length.
Replaces existing text with new text in a text string.
- text -text for which you want to substitute characters. (Required)
- old_text -the text you want to replace. (Required)
- new_text -the 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)
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.
Checks whether two strings are exactly same and returns true or false.EXACT is case-sensitive.
Add the cells specified by a given condition or criteria.
- range -the 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 the range are added together instead(Optional)
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, the range is used. (Optional)
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 an 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.
In short,I have listed mostly the formulas which are used in daily official work.
Please read below articles for reference.