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