SUMIF Formula

=SUMIF(A2:A9,5)
The formula above totals up the values from cell A2 to A9 based only on those cells that contain the value of 5. So if only cell A3 =5 and cell A5 = 5 as well and the other cells don't equal 5. Then the total value of the sumif formula would be 10. As the number 5 only appears twice in the cell range.

Read More..

Odd number formula

=Odd(6)
The answer to the formula above will be 7.
The Odd formula takes an even number like 6 and assigns it a value of the next available or higher odd number. So in this case the value in the brackets is an even number of 6 so the value that will be displayed is 7 as it is the next available Odd number for the number 6.

Read More..

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..