First, it's important to understand that the values in the Month column (B) are actual dates, formatted with the custom number format "mmm".
For example, B4 contains January 1, 2014, but displays only "Jan" per the custom number format.
The formula itself is based on the...Read more
In this example, the goal is to calculate the correct grade for each name in column B using the score in column C and the table to the right. For convenience only, grade (G5:G9) and score (F5:F9) are named ranges. This is...Read more
In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...Read more
Does cell A1 contain "apple"? This is a surprisingly tricky problem in Excel. In this article, we look at several options, based on the functions FIND, SEARCH, ISNUMBER, and COUNTIF.Read more
Compound interest is a financial concept that describes how an initial investment grows over time, taking into account not only the interest earned on the initial amount but also the interest earned on the interest itself. Compound interest allows your money to grow exponentially, which...Read more
The general formula for simple interest is:
interest=principal*rate*term
So, using cell references, we have:
=C5*C7*C6
=1000*10*0.05
=500
Read more
This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.
Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100...Read more
The DATEDIF function can handle a variety of "date difference" calculations to calculate the difference between two dates in years, months, and days. DATEDIF takes 3 arguments: start date, end_date, and "unit", which controls which result is returned.
In this case, we want days...Read more
This formula relies on the AND function to test two conditions at the same time:
If both conditions are TRUE, the formula returns TRUE...Read more
Working from the inside-out, the WEEKDAY function takes a date and returns a number between 1 and 7. With default settings, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.
The CHOOSE function simply maps numbers to values. The first argument is the number to...Read more