SUMPRODUCT

=SUMPRODUCT(Units,Prices)
Let's say cells A2 to A9 holds a list of units sold per order. Let's also say cells B2 to B9 holds a list of sales prices for each order. The sum product formula as shown adds up the total of cells A2 to A9 and multiplies it by the sum of cells B2 to B9. So if the total value of cell range A2:A9 is 10 and the total value of cell range B2:B9 is 30. The sum product value would be 300.
E.G.:
=SUMPRODUCT(A2:A9,B2:B9)

Read More..

Integer Formula

"=INT(300.89)" = 300
OR
=INT(A2)
The excel formula above effectively drops the decimal numbers on any value or value in a cell reference. So the value that will be displayed from 300.89 will be 300.

Read More..

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

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