Trend Formula

The Trend formula is used to forecast predicted growth or decline in most popularly sales figures of a business.
Theory:
=Trend(previous sales data range, previous months data range, new month number or cell reference)
Example:
=Trend($B$2:$B$13,$A$2:$A$12,C2)
For the formula above make sure you use absolute cell referencing with the dollars on both sides of the letters as shown, a part from the last cell reference being "C2" in this case. Then fill the formula down.
OR
=Trend($B$2:$B$13,$A$2:$A$12,13)
For the formula above make sure you use absolute cell referencing with the dollars on both sides of the letters as shown, a part from the last part which is just a number in this case "13". This paticular formula using the number 13 and not a cell reference is only useful for single next month prediction.
The table below is a sample data set. NB: T is for Trend Forecast and mn is for Month.
A       B          C        D
mn  sales    T.mn   T.sales
1      8685    13       5625
2      7865    14       5437
3      9765    15       5248
4      4455    16       5060
5      7865    17       4871
6      2334    18       4683
7      9876    19       4494
8      5647    20       4306
9      7864    21       4117
10    3456    22       3929
11    9823    23       3740
12    4567    24       3552

Read More..

Math Operators

---

= Equals
< Less Than
> Greater Than
<= Less Than OR Equal To
>= Greater Than OR Equal To
<> Not Equal To OR !=
* Wildcard
? Single Character Wildcard

---

Read More..

FORECAST Formula

Theory:
=FORECAST(Month Number to predict, Range of sales for prior 12 months, Range of month numbers for the year from 1 to 12)
Sales Table:
          A           B
1     Month   Sales
2     1            2457
3     2            8544
4     3            4145
5     4            6425
6     5            7885
7     6            8643
8     7            2354
9     8            4235
10   9            8664
11   10          4627
12   11          9753
13   12          9999
Example:
=FORECAST(8,B2:B13,A2:A13)
You should open a new excel workbook and populate  cells A1 to B13 with the headers and data listed above. Then in a separate cell enter the formula above to see your prediction for the month of August (8).

Read More..

COUNTA Formula - counts nonblank cells

Theory:
=COUNTA(Range)
Example:
=COUNTA(A2:A8)
The COUNTA formula counts the number of cells in a range that are not empty. it does not distinguish between different datatypes.

Read More..

Small Formula

Theory:
=SMALL(Range,Number)
Example:
=Small(A2:A9,2)
The SMALL function displays the smallest or lowest value from a range based on the number value written after the range reference. The number 2 in the formula above states that the formula should display the 2nd to last (or 2nd smallest) value from range A2 to A9. A number 1 would denote to display the last or 1st smallest value. The number 3 would denote to display the 3rd to last or 3rd lowest value and so on.

Read More..

Convert Formula to Number Value

Formula values normally can't be summed up to give a total sum for a range of numbers. To be able to display the total sum of a range written in formula you have to convert each formula cell to number format. To do this you have to use "=VALUE()".
Theory:
=Value(Formula)
Example
=Value(Mid(Cell,Start number,Number of characters))
Or
=Value(Mid(A2,1,1))

Read More..

Large Formula

Theory:
=LARGE(Range,Number)
Example:
=Large(A2:A9,2)
The Large function displays the largest or highest value from a range based on the number value written after the range reference. The number 2 in the formula above states that the formula should display the 2nd highest (or largest) value from range A2 to A9. A number 1 would denote to display the 1st highest value. The number 3 would denote to display the 3rd highest value and so on.

Read More..

Kurt Formula

=KURT(Range)
E.G.
=KURT(A2:A9)
The Kurt formula measures the Kurtosis of a range. It basically measures the steepness or flatness of a range of numbers. For instance if there is a sharp incline in a range of numbers followed by a steep decline then the Kurt value will be equal to or above 0. If there is a slow incline and also a gradual decline in a range of numbers over a period then the Kurt value will be equal to or below 0.

Read More..

Skew Formula

=Skew(A2:A9)
Skews looks at the deviation of a value from the mean. If list of values are mostly above the average then the skew value will be negative. If the values are mostly below the mean then the skew value will be positive.

Read More..

Avedev Formula

=AVEDEV(Range)
Example:
=AVEDEV(A2:A9)
The function above is short for average deviation. It calculates the mean of the range and also calculates its deviation altogether.

Read More..

SIN, COS and TAN Formulas

To use the SIN, COS and TAN to convert degree angles you also have to wrap the degree angle with the RADIANS formula. The SIN, COS and TAN formulas can only read RADIANS not Degrees. Examples are as follows:
=SIN(RADIANS(90))
=COS(RADIANS(9))
=TAN(RADIANS(9))

Read More..

Degrees and Radians

1 Radian = 57.3 degrees.
To convert degrees to radian
=RADIANS(Number of Degrees)
To convert radian to degrees
=DEGREES(Number of Radians)

Read More..

SUMIFS Formula

=SUMIFS(range_with_sum_value,criteria range_1,criteria_range_1_value,criteria range_2,criteria_range_2_value)
E.G.:
Looking at the formula below. Column A is the sales prices column. Column B is the types of products bought. Column C is the number of orders. The formula below is establishing the total sales price for Cakes that were bought more than once in each order.
=SUMIFS(A2:A9,B2:B9,"Cakes",C2:C9,>1)
Cells A2:A9 in the formula is the range to sum. The statement, 'B2:B9,"Cakes"' bases the cells being added up in cells A2:A9 solely on the related rows that say Cakes. The final statement, '>1', specifies to the formula to select those prices where the cake orders were greater than 1.

Read More..

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

Combine formula

=COMBIN(4,2)
The excel formula above will display the different kind of combinations you can have with the number 2 into 4. This is regardless of the order of the combinations. So if you had optional modules A,B,C and D and you had to take any 2 of them, then you would have 6 combinations being:
AB, AC, AD, BC, BD, and CD

Read More..

PERMUT formula

=PERMUT(3,2)
The excel formula above will display the number of ordered combinations you can have for 3 items by using 2 items out of 3. Let's say you had the options A, B,and C, which equates to 3 as above. Also, let's say you can take only two options in different orders as sugested by the 2 after the comma. So the formula above will give you 6 as the answer, as there are 6 possible ordered combinations. The following explains the ordered combinations you can have. So out of A, B, and C the 6 possible combinations you can have are:  AB, BA, BC,CB, AC and CA.

Read More..

Random number formula

=RAND()
The excel formula above will display random numbers with decimal places, primarily between 0 and 1. Which as you fill it down the excel.spreadsheet will generate random numbers. Such as, 0.13452789 and 0.97856453.

Read More..

Random number genarator formula for specific range

=RAND()*(65-1)+1
The excel formula above will generate random numbers between 1 and 65 as you fill it down an excel spreadsheet.  The (65-1)+1 bit tells excel to only select numbers between 1 and 65. Simplified it would be (higher number - lower number) + lower number. The RAND()* part randomises the numbers in the range. A sample result would be 56.90875721 or 1.34168538.

Read More..

PI Area formula

=(10/2)^2*PI()
The excel formula above calculates the area for the diameter of 10.  which gives the value of 78.53982. It is calculated by finding the radius of the diameter 10. Then multiply the radius to the power of 2. Then multiplying the resulting value by PI.

Read More..

PI diameter formula

=7/PI()
The excel formula above will give you the diameter of the circumference value of 7.  Which is 2.22816.

Read More..

PI circumference formula

=3*PI()
The excel formula above will display the circumference value for the diameter of 3.  Whch is 9.42477.

Read More..

PI formula

=PI()
The excel formula above will display the value of PI. Which is 3.141593.

Read More..

SIGN formula

The excel "Sign" formula indicates if the value of a cell is positive, negative, or zero. The Sign formula does this by displaying a '1' for a positive value higher than zero. Displays a '-1' for negative values below zero. Also displays '0' for values equal to zero. So, =SIGN(2) will display a value of 1.  =SIGN(0) will display a value of 0. Finally =SIGN(-7) will display a value of -1.

Read More..

Truncate to zero decimal place formula

Let's say the cell reference of A2 in the formula below has a value of 111.13:
=trunc(A2,0)
The value that would be displayed as a result of the formula above would be111. The number, 0 after the cell reference of A2 and the comma is telling excel to truncate the value of 111.13 to 0 decimal place. Giving  the value of 111.

Read More..

Truncate minus 2 decimal places

Let's say the cell reference of A2 in the formula below has a value of 111.13:
=trunc(A2,-2)
The value that would be displayed as a result of the formula above would be100. The negative number, -2 after the cell reference of A2 and the comma is telling excel to truncate the value of 111.13 back minus 2 decimal places. Giving  the value of 100.

Read More..

Truncate back minus 1 decimal place

Let's say the cell reference of A2 below has a value of 111.13:
=trunc(A2,-1)
The value that would be displayed as a result of the formula above would be. 110. The negative number, -1 after the cell reference of A2 and the comma is telling excel to truncate the value of 111.13 back minus 1 decimal place. Giving  the value of 110.

Read More..

Truncate to 1 decimal place formula

Let's say the cell reference of A2 below has a value of 111.13:
=trunc(A2,1)
The value that would be displayed as a result of the formula above would be. 111.1. The number 1 after the cell reference of A2 and the comma is telling excel to truncate the value of 111.13 back 1 decimal place. Giving 111.1

Read More..

Convert Number to Words (Indian Format)

How to create the sample function Called SpellNumber

1. Start Microsoft Excel.
2. Press ALT+F11 to start the Visual Basic Editor.
3. On the Insert menu, click Module.
4. Type the following code into the module sheet.

Read More..

Truncate to 2 decimal places formula

Let's say the cell reference of A2 below has a value of 1.13:
=trunc(A2,2)
The value that would be displayed as a result of the formula above would be. 1.13. The number 2 after the cell reference of A2 and the comma is telling excel to truncate the value of 1.13 to 2 decimal places. Giving 1.13.

Read More..

RAND formula rounded to 0 decimal place

=ROUND(RAND() * (65-1) +1,0)
The excel formula above will generate random numbers between 1 and 65 as you fill it down an excel spreadsheet.  The (65-1)+1 bit tells excel to only select numbers between 1 and 65. Simplified it would be (higher number - lower number) + lower number. The RAND()* part randomises the numbers in the range.  The ROUND(...,0) part rounds the random numbers to zero decimal place.  A sample result would be 57 or 1.

Read More..