## Archive for Math and trigonometry

How to use the MINVERSE function

The MINVERSE function calculates the inverse matrix for a given array. This function is useful for solving equations with multiple variables. […]

Count a given pattern in a cell value

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

The BASE function converts a number into a text representation with a given radix (base). Formula in cell C3: =BASE(10, […]

The ATANH function calculates the inverse hyperbolic tangent of a number. =ATANH(B3) Excel Function Syntax ATANH(number) Arguments number Required. Must be […]

The ATAN2 function calculates the arctangent of an angle using specific x- and y-coordinates. The returned angle is in radians […]

The ATAN function calculates the arctangent of a number. The returned angle is in radians between -pi/2 to pi/2. =ATAN(B3) Excel […]

The ASINH function calculates the inverse hyperbolic sine of a number. =ASINH(B3) Excel Function Syntax ASINH(number) Arguments number Required. Any […]

How to convert radians to fractions of pi

The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining […]

The ASIN function calculates the arcsine of a number. The angle is in radians and is between -pi/2 to pi/2. =ASIN(B3) Excel […]

The ACOTH function calculates the inverse hyperbolic cotangent of a number. Formula in cell C3: =ACOTH(B3) Excel Function Syntax ACOTH(number) […]

The ACOT function calculates the inverse cotangent of a number. The returned angle is given in radians from 0 (zero) […]

The ACOSH function calculates the inverse hyperbolic cosine of a number. Formula in cell C3: =ACOSH(B3) Excel Function Syntax ACOSH(number) […]

The ACOS function calculates the arccosine, or inverse cosine, of a number. Formula in cell C3: =ACOS(B3) Excel Function Syntax […]

The TRUNC function removes the fractional part of the number to an integer. Formula in cell C3: =TRUNC(B3) Excel Function […]

How to use the SERIESSUM function

The SERIESSUM function calculates the sum of a power series based on the following formula: SERIESSUM(x, n, m, a) = […]

The SECH function calculates the hyperbolic secant of an angle. Formula in cell C3: =SECH(B3) Excel Function Syntax SECH(number) Arguments […]

The COT function calculates the cotangent of an angle specified in radians. Formula in cell C3: =COT(B3) Excel Function Syntax […]

The COSH function calculates the hyperbolic cosine of a number. Formula in cell C3: =COSH(B3) Excel Function Syntax COSH(number) Arguments […]

The SINH function calculates the hyperbolic sine of a number. Formula in cell C3: =SINH(B3) Excel Function Syntax SINH(number) Arguments […]

The TANH function calculates the hyperbolic tangent of a number. Formula in cell C3: =TANH(B3) Excel Function Syntax TANH(number) Arguments number […]

How to use the SUMXMY2 function

The SUMXMY2 function calculates the sum of squares of differences of corresponding values in two arrays. Formula in cell F3: […]

How to use the SUMX2PY2 function

The SUMX2PY2 function calculates the sum of the sum of squares of corresponding values in two arrays. The sum of […]

How to use the SUMX2MY2 function

The SUMX2MY2function calculates the sum of the difference of squares of corresponding values in two arrays. Formula in cell F3: […]

The SUMSQ function calculates the sum of the squares of the arguments. Formula in cell D3: =SUMSQ(B3:B5) 2^2 = 4, […]

The SEC function calculates the secant of an angle. Formula in cell E3: =SEC(B3) Excel Function Syntax SEC(number) Arguments number […]

How to use the FLOOR.MATH function

The FLOOR.MATH function rounds a number down to the nearest integer or to the nearest multiple of significance. Formula in […]

How to use the FACTDOUBLE function

The FACTDOUBLE returns the double factorial of a number. Formula in cell C3: =FACTDOUBLE(B3) Excel Function Syntax FACTDOUBLE(number) Arguments number […]

How to use the DECIMAL function

The DECIMAL function converts a text representation of a number in a given base into a decimal number. Formula in […]

How to use the CEILING function

The CEILING function rounds a number up to its nearest multiple. The number is rounded down if both the number […]

The LCM function calculates the least common multiple. The least common multiple is the smallest positive integer that is a […]

How to use the MROUND function

The MROUND function calculates a number rounded to a given multiple. Formula in cell D3: =MROUND(B3,C3) Excel Function Syntax MROUND(number, […]

The SQRT function calculates the positive square root. Formula in cell C3: =SQRT(B3) Excel Function Syntax SQRT(number) Arguments number Required. […]

The LOG function calculates the logarithm of a number to a specific base. Formula in cell C3: =LOG(10) Excel Function […]

The LN function calculates the natural logarithm of a number. Natural logarithms are based on the constant e. Formula in […]

How to use the COMBINA function

The COMBINA function calculates the number of combinations for a given number of elements from a larger group of elements. Formula in […]

How to use the PRODUCT function

The PRODUCT function returns the product of the numbers given in the argument. Formula in cell D3: =PRODUCT(B3:C3) Excel Function […]

Rearrange cells in a cell range to vertically distributed values

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]

How to use the DEGREES function

The DEGREES function calculates degrees from radians. Formula in cell C3: =DEGREES(B3) Excel Function Syntax DEGREES(angle) Arguments angle Required. The […]

How to use the RADIANS function

The RADIANS function converts degrees to radians. Formula in cell C3: =RADIANS(B3) Excel Function Syntax RADIANS(angle) Arguments angle Required. The […]

The PI function returns the number pi (¶). Formula in cell B3: =PI() Excel Function Syntax PI() Arguments PI function […]

The POWER function calculates a number raised to a power. Excel Function Syntax POWER(number, power) Arguments number Required. The number […]

The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]

The TAN function calculates the tangent of an angle. Formula in cell C3: =TAN(B3) Excel Function Syntax TAN(number) Arguments number […]

The COS function calculates the cosine of an angle. Formula in cell C3: =COS(B3) Excel Function Syntax COS(number) Arguments number […]

The SIN function calculates the sine of an angle. Formula in cell C3: =SIN(B3) Excel Function Syntax SIN(number) Arguments number […]

The EVEN function rounds a number up to the nearest even whole number. An even number is a number that […]

Returns e raised to the power of a number, e equals 2.71828182845904. Example, e^2 equals 7.389056099 Formula in cell C3: […]

How to use the RANDBETWEEN function

Returns a random whole number between the numbers you specify. This function is volatile meaning a new random number is […]

The FACT function returns the factorial of a number. Formula in cell C3: =FACT(B3) Example, 3! = 3*2*1 = 6 […]

Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to […]

How to use the COMBIN function

The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]

How to use the AGGREGATE function

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]

The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list […]

Converts negative numbers to positive numbers, in other words, the ABS function removes the sign. Excel Function Syntax ABS(number) Arguments […]

How to use the SUMIFS function

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]

Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3.

SUMPRODUCT and nested IF functions

I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]

The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3.

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

SUMPRODUCT – multiple criteria

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]

Learn how to use the SUBTOTAL function

The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

The SUMIF function sums values based on a condition. The condition can be applied to the values being summed or […]

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

List permutations with repetition and how many to choose from

Noel asks: Is there a way where i can predict all possible outcomes in excel in the below example. Total […]

Count overlapping days across multiple date ranges

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Working with overlapping date ranges

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical […]

You are about to send your company products to customers. You have boxes you can send the products in. You […]

Permutations with and without repetition

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

The RAND function calculates a random real number greater than or equal to 0 and less than 1. The function […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]

David asks: Hi, I would like to use this example with my data set however I'd like to visually show […]

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

How to create random numbers, text strings, dates and time values

In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

Select numbers in each permutation

Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]

Vlookup visible data in a table and return multiple values

This post describes how to search visible values and return multiple values from a table. Some rows are hidden because […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

List permutations without repetition [UDF]

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

List permutations with repetition [UDF]

This blog post describes how to create permutations. Repetition is allowed. Vba code: Function ListPermut(num As Integer) 'Permutations with repetition […]

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Dynamic team generator in excel

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Identify overlapping date ranges

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Sum based on multiple criteria

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Sequencing and numbering of batches

Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Sum values in a range where adjacent cell value equals a criterion

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: […]

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Create a repeating list of numbers from 1 to Nth value

Question: How do I create a repeating list of numbers from 1 to Nth value? In my case from 1 […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]