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
In this example, the goal is to get the first day of the month based on any valid date. This problem can be solved by combining the EOMONTH function with simple addition.
The ...Read more
In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the SUMPRODUCT function to get...Read more