The MEDIAN function has no built-in way to apply criteria. Given a range, it will return the MEDIAN (middle) number in that range.
To apply criteria, we use the IF function inside MEDIAN to "filter" values. In this example, the IF function filters by group like this:
...Read more
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.Read more
This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:
type,G$4
where type is the named range D5:D14...Read more
In the example shown, row 5 is a header row and which contains a series of valid dates, formatted with the custom number format "d". With a static date in D5, you can use this formula in E5 (copied across) to populate the calendar header in row 5:...Read more
In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and...Read more
In this example, the goal is to construct a formula that will subtotal the amounts in column D by client and month as seen in the range G5:I8. A big part of the problem is to set up the proper references so that the formula can be entered once, and copied throughout G5:I8. The solution...Read more
The "hashtag" error, a string of hash or pound characters like ####### is not technically an error, but it looks like one. In most cases, it indicates the column width is too narrow to display the value as formatted. You might run into this odd looking result in several situations:
The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "range intersect" operator and the #NULL! error is reporting that the...Read more
The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas that return the #NUM error. In general, the fixing the #NUM! error is a matter...Read more
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function expecting a number receives text value, or when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of...Read more