Note: this example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all data in an Excel Table called "data...Read more
This formula relies on a helper column, which is column E in the example shown. The formula in E5, copied down, is:
=(COUNTIFS($B$5:B5,B5)=1)+0
This formula returns a 1 for new customers and a 0 for repeat customers, and is...Read more
This formula uses an expanding range for the criteria range inside COUNTIFS:
COUNTIFS($B$5:B5,B5)
Because the first reference is absolute and the second reference is relative, the range expands...Read more
One of the VLOOKUP function's key limitations is that it can only look up values to the right. In other words, the column that contains lookup values must sit to the left of the values you want to retrieve with VLOOKUP. There is no way to override this...Read more
In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function. However, you can also use the ...Read more
In this example, the sum range is the named range "time", entered as an Excel time in hh:mm format. The first criteria inside SUMIFS includes dates that are greater than or equal to week date in column F:
date,">="...Read moreA circular reference occurs when a formula refers directly to its own cell, or refers to another cell that depends on the original cell. This creates an infinite loop that cannot be resolved. For example, if cell A1 contains a formula that refers to B1, and B1 contains a formula that refers to...Read more
This example shows a line chart plotted with over 8000 data points. The data itself is daily stock market information for Microsoft Corporation over a period of more than 30 years. Only the closing price is plotted. When you first create a line chart with this much data, the x-axis will be crowded with labels. The key is to adjust the bounds and units for the in the Axis options area. In this case, the axis is formatted as a date axis.
In this example, the goal is to calculate an average for each group and region in the data as shown in the worksheet. For convenience, data is an Excel Table in the range B5:D16. This problem can be easily solved with the...Read more
The goal is to verify the input of all required values before calculating a result. In the worksheet shown, the SUM function is used as an example only. You can use the same approach with any formula to prevent calculation until all required values are available. The logic can be...Read more