If you are a
regular user of Excel and deal with lot of text data, then mastering text
functions in Excel is crucial. Excel has in-built text functions to help
cleansing data for you. Below are some the text functions in excel helpful for quick
data cleansing
1.CLEAN: CLEAN function...
Showing posts with label formulas. Show all posts
Showing posts with label formulas. Show all posts
How to create user-defined functions in Excel
in formulas
with
No comments
Excel allows users to create custom functions. This enables user to extend the functionalities of Excel. In the below example, a Boolean function created to accepts a cell as a parameter and returns TRUE if the cell has color FALSE if the cell doesn't have any color. Let us call this function...
How to convert mailing list in Excel column into a string using Collections?
Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.
First store all...
How to use curly braces in non-array formula in Excel
Curly Braces are generally used in array formulas in Excel. Array formulas are entered by pressing CONTROL+SHIFT+ENTER and Excel automatically inserts curly-braces in formula. Array formulas are useful when one has to get results from multiple set of values into a single cell or range of values.
Array Formula:
For example, Consider Range A1 through A3 has text "No", "Yes" and "None". If we have...
The Difference between Cumulative Distribution Function (CDF) and Probability Density Function (PDF)
Cumulative Distribution Function (CDF) vs Probability Distribution Function (PDF)
The Cumulative Distribution Function (CDF) of a random variable 'X' is the probability that the variable value is less than or equal to 'X'. It is the cumulative of all possible values between two defined ranges.On the other hand, Probability Distribution Function (PDF) is the probability of random variable...
The difference between DAYS and DAYS360 formulas
DAYS360:
The DAYS360 function is used to calculate number of days between two dates. But it is based on the assumption that each month in a year has 30 days. This method is adopted in some financial institutions for the calculation of interest and other accounting purposes.
DAYS():
However Excel 2016 version has DAYS() formula which calculates the number of calendar days between two dat...
How to enter formula with relative and absolute reference using VBA
Relative vs Absolute:
To enter a formula using VBA into a single cell, We use Range().Formula property and assign it to worksheet function.
For Example, VBA code to enter VLOOKUP formula in cell B1 is
RANGE("B1").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"
Suppose,...
CAGR calculation in Excel
CAGR:
CAGR - Compounded Annual Growth Rate is a measure of growth over a period of time. It is usually calculated for number of years. Businesses use CAGR to measure the growth in their revenue over a period of time. In finance CAGR is used as a measure that indicates appreciation in the value of...
How to convert time format in to Minutes in Excel
How to convert time format in to Minutes in Excel:
Consider an example where Start Time, End Time and Total Time Taken for a set of activities are stored in a standard Excel Time format (i.e. hh:mm:ss). Here TOTAL TIME = END DATE - START DATE. In column F, let us calculate total time taken in Minutes.
...
How to use SUMPRODUCT to calculate mean of Discreet Distribution
Discreet Distribution:
A Discreet Distribution is a distribution constructed from a random variable produced by non-negative whole numbers. MEAN or AVERAGE is nothing but Expected Value of a Discreet Distribution.
The MEAN of any discreet distribution is calculated by taking the sum of product of...
How to use wildcard characters in Excel formulas
Wildcard Characters:
Wildcard characters are useful when we have partial search criteria to extract or manipulate data. In Excel, we use "?" and "*" are wildcard characters. "?" represents single character, and "*" represents series of characters.
Wildcard characters are used with Excel functions,...
How and when to use TRIMMEAN function in Excel
in formulas
with
No comments
TRIMMEAN is used to calculate Mean or Average of data points excluding the outliers or extreme values. Whenever we calculate Mean including outliers, We may get values that are not accurate and does not represent the fact. The reason for this is MEAN tend to get affected by extreme values. One big...
How to use GESTEP Function in Excel
in formulas
with
No comments
GESTEP Function:
GESTEP(NUMBER, STEP) Function checks two parameters, NUMBER and STEP value (numeric) and returns
1) 1 (if NUMBER >= STEP)
...
Total Cycle Time calculation in Batch Production System
in formulas
with
No comments
Total Cycle Time calculation in Batch Production System:
Consider a scenario in batch production (or processing) system where, a batch size of 100 jobs has to undergo three different processes namely P1, P2 and P3 with cycle time of 10, 17, 9 minutes respectively, before they are ready to be dispatched.
In...
How to Determine and Interpret Correlation Co-efficient in Excel
in formulas
with
No comments
Correlation Co-efficient is one of the important and useful tool in determining the relationship between two data sets (or two variable). Correlation co-efficient is denoted by "r" and its value ranges from -1 to +1.
Formula to calculate correlation co-efficient is
If the value of "r"...
Alternatives to QUOTIENT formula in Excel
in formulas
with
No comments
Quotient formula returns integer portion of a division. Its syntax is "QUOTIENT(numerator, denominator)". Similarly INT and ROUNDDOWN formulas can be used as an alternative to QUOTIENT.
For example:
119/15 = 7.93
In this example quotient is 7. Now, lets use above three formulas and check results
"=QUOTIENT(119,5)"...
How to reverse the order of cells in Excel?
in formulas
with
No comments
In this post let us learn to get the order of adjacent cells reversed. Enter numbers from 1 to 10 in Column B from B2 to B11. Now, from C2 to C11 we have to get numbers from 10 back to 1. There are many ways to get this result. I used combination of OFFSET and COUNT formulas to get the output.
In...
Squareroot, cuberoot, Nth root of a number in Excel
in formulas
with
No comments
Excel has in-built function to find squareroot of a number i.e. SQRT(number), but to find cuberoot, 4th root, 5th root, We use "POWER" function.
For example: cube root of 4 is "=POWER(4,1/3)"
4th root of 4 is "=POWER(4,1/...
3 Ways to enter formulas in Excel
in formulas
with
No comments
There are 3 ways in which you can enter formulas into an Excel cell. Select any cell and type one of the following sign and enter the name of the function and press "Enter".
1) = (equal sign)
2) + (Plus sign)
3) @ (at sign)
For Example, Enter values 3, 4 and 5 in cells A1, A2 and A3 respectively. Now, type (without quotes)
"=SUM(A1:A3)"
"+SUM(A1:A3)"
"@SUM(A1:A3)"
in cells B1,B2...