The FORECAST.ETS.SEASONALITY function returns the length in time of a seasonal pattern based on existing values and a timeline. FORECAST.ETS.SEASONALITY can be used to calculate the season length for numeric values like sales, inventory, expenses, etc. exhibit a seasonal...Read more
Note: later versions of Excel include a native histogram chart, which is easy to create, but not as flexible to format. The example on this page shows one way to create your own histogram data with...Read more
One of the trickier problems in Excel is to count rows in a set of data with "OR logic". There are two basic scenarios: (1) you want to count rows where a value in a column is "x" OR "y" (2) you want to count rows where a value, "x", exists in one column OR another.
In this example, the...Read more
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.
The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array...Read more
The LARGE function is an easy way to get the nth largest value in a range:
=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest
In this example, we can use the LARGE function to get a highest score...Read more
In a nutshell, this formula uses the LARGE function to find the nth largest value in a set of data. Once we have that value, we plug it into a standard INDEX and MATCH formula to retrieve the associated name. In other words, we use the nth largest value...Read more
In this example, the goal is to lookup data to the left of an ID that appears as the last column in the table. In other words, we need to locate a match in column E, then retrieve a value from a column to the left. This is one of those problems that is...Read more
The formulas shown in the example all use the AVERAGE function with a relative reference set up for each specific interval. The 3-day moving average in E7 is calculated by feeding AVERAGE a range that includes the current day and the two previous days like this:
...Read more
The key to solving this problem is to realize that the solution requires a specific kind of rounding. We can't just round to the "nearest" .45 or .95 value. In fact, the first step is to round up to the nearest half dollar (.50). The second step is to subtract 5 cents ($0.05...Read more