Calculating a Trend

Use the TREND function to calculate a linear trend. This fits a straight line, using the method of least squares, to known dependent and independent variables and returns a new set of dependent variables. A trend is a statistical method of projecting for the future a set of outcomes based on data from the past. To fully understand the calculation method and interpret the results correctly you should consult the Excel documentation and also a publication dealing with statistical theory, search for the subject linear regression.

TREND(known_y's,known_x's,new_x's,const)

Where Known_y's and Known_x's are the known dependent and independent variables respectively. New_x's are the new independent variables for which you want TREND to return the corresponding dependent variables.

Single Cell Array Formulas

This is an example of where a formula has to be entered into a single cell as an array in order for it to calculate correctly. The formula does not return an array of values, rather the values that it is calculating need to be treated as an array. Even if you do not fully understand exactly how the formulas calculate you can nevertheless use the examples as a template to produce this type of calculation. In other words; "copy and paste" and change the cell references.

Total the Units where the Product is Soap and the Country is UK:

=SUM(IF((B2:B11="Soap")*(A2:A11="UK"),C2:C11))

Total the Values where the Country is France or Spain:

=SUM(IF((A2:A11="France")+(A2:A11="Spain"),D2:D11))

Common problems

All examples assume that the first cell is A1.

Bad Negatives

Imported data sometimes has the negative numbers with the minus sign on the right. This is a common problem with data imported from SAP.

=VALUE(IF(RIGHT(A1,1)="-",SUBSTITUTE(RIGHT(A1,1)&A1,"-","",2),A1))

Remainders

Areas to be tiled come in square meters but tiles come in packs of 12. Work out the number of packs to be ordered and the wastage. Floor or ceiling tiles.

League tables

Some people have to work harder at their formulas for Fantasy Football league than they do for their normal work. We have a table of names and total scores and we want to show a separate league table without having to copy and sort the table.