Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term), and the payment amount per period. One use of the PV function is to calculate the original loan amount, when given the other 3 components.
For this example, we want to find...Read more
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 RATE function is to calculate the periodic interest rate when the amount,...Read more
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
