## Archive for Statistical

How to use the AVERAGEIFS function

The AVERAGEIFS function returns the average of cell values that are TRUE for multiple criteria. Formula in cell F6: =AVERAGEIFS(D3:D8,B3:B8,F3,C3:C8,G3) The […]

How to use the AVERAGEIF function

The AVERAGEIF function returns the average of cell values that are valid for a given condition. Formula in cell D3: […]

The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts […]

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in […]

If cell contains multiple values

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

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

How to ignore zeros using the SMALL function

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros.

SMALL function – multiple criteria

The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]

The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems […]

Learn to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

SMALL function with duplicates

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

The COUNTIF function is very capable of counting non-empty values. Column B above have a few blank cells, they are […]

The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]

Use IF + COUNTIF to perform numerous conditions

The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF […]

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

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 […]

Prevent duplicate records [Data Validation]

Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share […]

Returns how many times values exists in a given range. Note, this function returns an array of values.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]

Count unique distinct records in Excel

This example sheet has 3 columns with some random data. It is quite complicated trying to manually count unique distinct […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

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

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

How to count unique distinct occurrences for each date

Question: How to count unique distinct values on the same date? Answer: Array formula in D3: =SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), […]

Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 […]

Create unique distinct year and months from dates

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Lookup min max values within a date range

This post demonstrates how to find minimum and maximum value using two conditions. In this case they are date conditions […]

Count matching cell values in two columns

Question: How do I count common cell values in two columns? Answer: Array formula in G12: =SUM(IF(FREQUENCY(COUNTIF(List2, "<"&List2), COUNTIF(List2, "<"&List2))>0, […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

Count unique records between two dates

This blog article is one out of four articles on the same subject. Most common value between two dates in […]

How to quickly find the maximum or minimum value [Formula]

Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter unique distinct row records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]

Find the smallest value in a list but bigger than a number

Problem: Find the smallest value in a list but it has to be bigger than 10? Answer: MINIFS function [Excel […]