The goal in this example is to sum Prices in column C when the Items in column B contain an asterisk (*). The challenge is that the asterisk (*) is reserved as a wildcard in functions like the SUMIFS function, so you can't match a literal occurrence of this character without using a...Read more
This formula uses the value in cell E5 for a lookup value, the named range "key" (H5:I9) for the lookup table, 2 to indicate "2nd column", and 0 as the last argument indicate exact match. You can also use FALSE instead of zero if you like.
VLOOKUP simply...Read more
If you've ever wondered whether learning Excel formulas is worth your time, this list is for you. Formulas are the glue that hold spreadsheets together all over the world, and your skill with them can help you in many ways.Read more
This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in the data:
MATCH(B5:B11,B5:B11,0)...Read moreIn this example, the goal is to calculate an average of the top 3 quiz scores for each name listed in column B. For reference, column H has a formula that calculates an average of all 4 scores. This is a slightly tricky problem, because it's not obvious how to limit the scores included in the...Read more
In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the...Read more
EDATE can get the "same date" in the future or past, based on the number of months supplied. When 12 is given for months, EDATE gets the same date next year.
To get the same date in a previous month, use -12:
=...Read moreThe EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. When 1 is given for months, EDATE returns the same date in the next month. Notice that...Read more
In this example, the goal is to get the total number of days in a month based on any date in the month. This problem can be solved by combining the DAY function with the EOMONTH function.
The ...Read more
The EOMONTH function returns the last day of a month based on a given date. The 2nd argument is months, which specifies how many months in the future or past to move before returning the last day. By traveling back 2 months, then adding one day, we can calculate the first day of the...Read more