Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan.
For this example, we want to calculate the number of...Read more
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely.
In this example, SUMPRODUCT is...Read more
Working from the inside out, we first "normalize" row numbers to begin with 1 using the ROW function and an offset:
ROW()-offset
In this case, the first row of data is in row 5, so we use an offset of 4:
...Read more
In this example, the goal is to count rows where the value in column one is "A" or "B" and the value in column two is "X", "Y", or "Z". In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to...Read more
This is a more advanced formula. For basics, see How to use INDEX and MATCH.
Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values...Read more
In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed.
In Excel, it's difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula...Read more
This formula is a classic example of a nested IF formula that tests threshold values in ascending order. To match the schedule shown in G5:G11, the formula first checks the late by time in D5 to see if it's less than 5 minutes. If so, zero points are assigned:
...Read more
By definition, the nth root of a number can be calculated by raising that number to the power of 1/n. Exponents are entered using the exponentiation operator (^), with a number on the left and power on the right. So, in this example we get the numbers from...Read more