Explanation
At the core, this is a simple formula that simply divides the total amount by the number of months given:
=amount/months
The trick is to “cancel out” this amount in months where it doesn’t apply.
To do this, we use this logical expression:
AND(E4>=start,E4<(start+months))
Here we use the AND function to test each month in row 4 to see if it’s both greater than or equal to the given start month, and less than the end month, calculated by adding the start month to total months.
AND will return TRUE only when both conditions are TRUE, and return FALSE in another other case. This effectively zeros out calculations in months that fall outside the range of interest. This works because during math operations, FALSE is coerced to zero, and TRUE is coerced to 1.
Without named ranges
The formula in the example shown uses three named ranges . Without these named ranges, the formula can be written like this:
=$C$4/$C$5*AND(E4>=$C$6,E4<($C$6+$C$5))
Explanation
The SQRT function is fully automatic and will return the square root of any positive number. For example, to get the square root of 25, you can use:
=SQRT(25) // returns 5
To get the square root of 16:
=SQRT(16) // returns 4
To get the square root of a number in cell A1:
=SQRT(A1) // square root of A1
Negative numbers
If you give SQRT a negative number, it returns a #NUM! error:
=SQRT(-4) // returns #NUM!
To use the SQRT function with negative numbers you nest the ABS function inside SQRT like this:
=SQRT(ABS(-4)) // returns 2
The ABS function converts the negative number to a positive number and returns the result to the SQRT function, which calculates a final result.
Exponent operator (^)
Another way to get the square root of a number in Excel is to use the exponent operator , the caret (^). To return the square root of a number in A1, you can use a formula like this:
=A1^(1/2) // square root
The screen below shows how this formula looks in a worksheet:
Nth root
Excel does not have a built-in function to get the nth root of a number. However, you can calculate the nth root of a number by raising the number to the power of 1/n:
=A1^(1/n) // nth root
The screen below shows this formula in use:
Square root with POWER
You can get also get the square root or nth root of a number with the POWER function . POWER is a general function for raising a number to a given power, for example:
=POWER(2,2) // returns 4
=POWER(2,3) // returns 8
=POWER(2,4) // returns 16
The general form for getting the nth root with POWER is:
=POWER(A1,1/n) // nth root
For example:
=POWER(A1,1/2) // square root
=POWER(A1,1/3) // cube root
=POWER(A1,1/4) // fourth root
The screen below shows how the POWER function can be used to calculate square root of the numbers in column A:
