Even Number Formula

=Even(5)
The answer to the above formula is 6.
The Even number formula works by rounding the value given as a cell reference or value up to an even number. So, as above if you have an odd number of 5 the formula above will round it up to the next available even number, which is 6.

Read More..

Percentage rates formula

=RATE(number of payments, monthly paymet amount as negative value, total amount owed)
E.G. =RATE(27,-890,10000) = 7.70%
The percentage value of 7.70% tells you the rate of interest that you will pay during the course of 27 payments; at the amount of 890; with a total initially owed of 10,000.

Read More..

PMT Formula

=PMT(Interest Rate/12, Number of Payments,Total Amount Borrowed)
The above PMT formula is used to calculate the amount you will pay inclusive of interest in a period.

Read More..

Transpose Formula

=Transpose(A1:B3)
The transpose formula flips horizontal data vertically and flips vertical data horizontally. The transpose formula mirrors the data being referenced as stated above.  So if you have data running 3 columns and 4 rows, the transpose formula mirrors that data over 3 rows and 4 columns. If you have data on 2 columns and 2 rows the transpose formula will display the data as 2 rows and 2 columns as it is equal in length horizontally and vertically.
So, the formula '=Transpose(A1:B3)' stems over 2 columns being A and B and 3 rows. So, to transpose the data in a formula, you would have to select 2 rows and 3 columns. So as an example, place some data in cells A1 to A3 and also in cells B1 to B3.  Then highlight cells C1 to E2 and with the cells highlighted type =Transpose(A1:B3).  Then without typing or clicking anything else hit Ctrl + Shift + Enter. The data will now be transposed horizontally into cells C1 to E2.

Read More..

Subtotal Average formula

=SUBTOTAL(1,A2:A4)
Let us say that the value in A2 = 4, A3 = 4, and A4 = 4. Also, the number 1 with a comma before the cell range means use the Average function to calculate the Subtotal. The answer displayed will be 4. As 4 is the average of cell range A2 to A4 as stated above.
The subtotal function can hold up to 255 ranges or numbers.

Read More..

Modulus formula to find the remainder

=MOD(value, divisor)
Examples:
=MOD(3,2)
OR
=MOD(A2,B2)
Let's say cell A2 = 3 and cell B2 = 2 as the first modulus formula above.
The answer would be a remainder of 1. As 3 divided by 2 will give a remainder of 1. That is how the formula works.

Read More..

Product formula

=PRODUCT(A2,B2,C2)
Let's say the value in cell A2 is 2, in cell B2 is 2 and in cell C2 is 3. The Product formula is a function that multiplies the cell references separated by commas with each other. You can use a combination of numbers and cell references. You can have up to 255 comma separated values or ranges. The value that will be displayed by the formula above will be 12. The product formula works like this: 2 x 2 x 3 which is A2 x B2 x C2.

Read More..