EXCEL ADVANCE FORMULA
EXCEL
Let's take
each of the advanced Excel formulas one by one and explain their functions and
usage in more detail:
1.
**ARRAY FORMULA**: Excel uses array formulas differently from Google Sheets.
Array formulas allow you to perform operations on multiple values at once. In
Excel, you must press `Ctrl + Shift + Enter` to execute an array formula. For
example, `{=A1:A5 * B1:B5}` multiplies corresponding cells in two ranges.
2.
**FILTER**: Returns an array that matches a given condition. For example,
`=FILTER(A1:A10, B1:B10 > 0)` returns the values in `A1:A10` where the
corresponding values in `B1:B10` are greater than 0.
3.
**UNIQUE**: Returns a list of unique values from a range. For example,
`=UNIQUE(A1:A10)` will return a list of unique values from the range `A1:A10`.
4. **SORT**:
Sorts an array based on specified columns. For example, `=SORT(A1:A10, 1, 1)`
sorts `A1:A10` in ascending order.
5.
**SORTBY**: Sorts a range based on values from another range. For example,
`=SORTBY(A1:A10, B1:B10, 1)` sorts `A1:A10` based on the values in `B1:B10`.
6.
**XLOOKUP**: Searches a range and returns the value for the first match found.
For example, `=XLOOKUP("value", A1:A10, B1:B10)` searches for
"value" in `A1:A10` and returns the corresponding value from
`B1:B10`.
7. **LET**:
Lets you define named ranges within a formula for easier readability. For
example, `=LET(x, A1, y, B1, x + y)` allows you to define `x` as `A1` and `y`
as `B1`, then returns the sum.
8.
**SEQUENCE**: Generates an array of numbers in sequence. For example,
`=SEQUENCE(5, 2, 1, 1)` returns an array of 5 rows and 2 columns, starting at 1
and increasing by 1 each time.
9.
**RANDARRAY**: Generates an array of random numbers. For example,
`=RANDARRAY(5, 2)` returns a 5 by 2 array of random numbers between 0 and 1.
10.
**TEXTJOIN**: Joins multiple text strings into one string using a specified
delimiter. For example, `=TEXTJOIN(", ", TRUE, A1:A5)` joins the
values in `A1:A5` with a comma and a space as the delimiter.
11.
**CONCAT**: Concatenates a list or range of text strings. For example,
`=CONCAT(A1:A5)` combines the values in `A1:A5` into one text string.
12. **IFS**:
Tests multiple conditions and returns a value based on which condition is true.
For example, `=IFS(A1 > 10, "Greater than 10", A1 < 5,
"Less than 5", TRUE, "Between 5 and 10")` returns a
corresponding value based on the range of `A1`.
13. **SWITCH**:
Evaluates an expression and returns a value based on a list of cases. For
example, `=SWITCH(A1, 1, "One", 2, "Two", 3,
"Three", "Other")` returns the corresponding text based on
the value in `A1`.
14.
**FILTERXML**: Extracts data from an XML string using an XPath expression. For
example,
`=FILTERXML("<root><item>value</item></root>",
"/root/item")` extracts the value from the XML.
15.
**FORECAST.ETS**: Uses the Exponential Smoothing method to predict future
values. For example, `=FORECAST.ETS(DATE(2023, 1, 1), A2:A10, B2:B10)`
forecasts future values based on historical data.
16.
**HYPERLINK**: Creates a hyperlink to a specified URL. For example,
`=HYPERLINK("http://www.example.com", "Click Here")`
creates a clickable link with the text "Click Here."
17.
**RANK.EQ**: Returns the rank of a number in a list. For example, `=RANK.EQ(A1,
A1:A10)` returns the rank of `A1` in the range `A1:A10`.
18.
**RANK.AVG**: Returns the average rank of a number in a list when there are
ties. For example, `=RANK.AVG(A1, A1:A10)` returns the average rank of `A1` in
the range `A1:A10`.
19.
**COVARIANCE.S**: Calculates the sample covariance between two datasets. For
example, `=COVARIANCE.S(A1:A10, B1:B10)` calculates the sample covariance
between the ranges `A1:A10` and `B1:B10`.
20.
**COVARIANCE.P**: Calculates the population covariance between two datasets.
For example, `=COVARIANCE.P(A1:A10, B1:B10)` calculates the population
covariance between the ranges `A1:A10` and `B1:B10`.
21.
**LOGNORM.INV**: Returns the inverse of the log-normal cumulative distribution
function. For example, `=LOGNORM.INV(0.5, 1, 0.5)` calculates the inverse of
the log-normal distribution.
22.
**POISSON.DIST**: Returns the Poisson distribution probability. For example,
`=POISSON.DIST(5, 2, TRUE)` calculates the cumulative Poisson probability with
mean `2` and value `5`.
23.
**BINOM.DIST.RANGE**: Calculates the probability of success in a range of
binomial distributions. For example, `=BINOM.DIST.RANGE(10, 0.5, 4, 6)`
calculates the probability of having 4 to 6 successes in 10 trials with a
probability of success of 0.5.
24.
**CUBEMEMBER**: Retrieves a member or tuple from an OLAP cube. For example,
`=CUBEMEMBER("SalesCube", "[Time].[Year].&[2023]")`
returns a member from the "SalesCube" cube.
25.
**CUBESET**: Returns a set of members or tuples from an OLAP cube. For example,
`=CUBESET("SalesCube",
"[Product].[ProductCategory].Members")` returns a set of members from
the "SalesCube" cube.
26.
**CUBEVALUE**: Returns the aggregated value from an OLAP cube. For example,
`=CUBEVALUE("SalesCube", "[Measures].[Sales Amount]")`
returns the aggregated sales amount from the "SalesCube" cube.
27.
**DAYS**: Returns the number of days between two dates. For example,
`=DAYS(DATE(2024, 1, 1), DATE(2023, 1, 1))` returns the number of days between
January 1, 2023, and January 1, 2024.
28.
**DATEDIF**: Calculates the difference between two dates in days, months, or
years. For example, `=DATEDIF(DATE(2023, 1, 1), DATE(2024, 1, 1),
"d")` returns the number of days between January 1, 2023, and January
1, 2024.
29.
**STDEV.P/STDEV.S**: Calculate the standard deviation of a dataset. `STDEV.P`
calculates population standard deviation, while `STDEV.S` calculates sample
standard deviation. For example, `=STDEV.P(A1:A10)` calculates the population
standard deviation of `A1:A10`, while `=STDEV.S(A1:A10)` calculates the sample
standard deviation.
These
advanced formulas enable you to perform complex calculations and data analysis
tasks more efficiently in Excel. Let me know if you need examples or
explanations for specific use cases!
Comments
Post a Comment