CAGR stands for Compound Annual Growth Rate. CAGR is the average rate of return for an investment over a period of time. It is the rate of return required for an investment to grow from the starting balance to the ending balance, assuming profits are reinvested each year, and...Read more
Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The NPV function in Excel only calculates the present value of uneven cashflows, so the initial cost must be handled explicitly.
One way to calculate Net Present Value in...Read more
In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:
Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
This formula breaks ties with a simple approach: this first tie in a list "wins" and is assigned the higher rank. The first part of the formula uses the RANK function normally:
=RANK(C5,points)
Rank returns a computed rank, which will...Read more
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the input is rejected with a popup message.
In this case, we have previously...Read more
The first thing this formula does is check the date in column B against the start date:
=IF(B4>=start
If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the...Read more
The COMPLEX function returns the string representation of a complex number. For example:
=COMPLEX(4,3) // returns "4+3i"
To use the "j" instead of "i":
=COMPLEX(4,3,"j") // returns "4+3j"...Read moreThe Excel IMPRODUCT function returns the product of one or more complex numbers. For example:
=IMPRODUCT("1+2i", "3+5i") // returns -7+11iExcel handles complex numbers as strings formatted
...Read more
The Excel IMDIV function returns the quotient of two complex numbers. For example, given "-7+11i" and "3+5i" as input, the function returns the quotient "1+2i", which is the result of dividing the first number by the second.
=IMDIV(COMPLEX(-7,11),...Read more