TREND Function (Functions in Excel) WHAT IS CONST b ?
Historical Data | Predicted Values | ||||
Month | Sales | Month | Sales | ||
1 | £1,000 | 7 | £4,940 | {=TREND(C8:C13,B8:B13,E8:E13)} | |
2 | £2,000 | 8 | £5,551 | {=TREND(C5:C10,B5:B10,E5:E10)} | |
3 | £2,500 | 9 | £6,163 | {=TREND(C5:C10,B5:B10,E5:E10)} | |
4 | £3,500 | 10 | £6,774 | {=TREND(C5:C10,B5:B10,E5:E10)} | |
5 | £3,800 | 11 | £7,386 | {=TREND(C5:C10,B5:B10,E5:E10)} | |
6 | £4,000 | 12 | £7,997 | {=TREND(C5:C10,B5:B10,E5:E10)} | |
What Does It Do ? |
This function predicts values based upon three sets of related values. |
The prediction is based upon the Linear Trend of the original values. |
The function is an array function and must be entered using Ctrl+Shift+Enter. |
Syntax |
=TREND(KnownYs,KnownXs,RequiredXs,Constant) |
The KnownYs is the range of values, such as Sales Figures. |
The KnownXs is the intervals used when collecting the data, such as Months. |
The RequiredXs is the range for which you want to make the prediction, such as Months. |
Formatting |
No special formatting is needed. |
Example |
The following tables were used by a company to predict when they would start to |
make a profit. |
Their bank manager had told the company that unless they could show a profit by the |
end of the next year, the bank would no longer provide an overdraft facility. |
To prove to the bank that, based upon the past years performance, the company would |
start to make a profit at the end of the next year, the =TREND() function was used. |
The historical data for the past year was entered, months 1 to 12. |
The months to predict were entered, 13 to 24. |
The =TREND() function shows that it will be month 22 before the company make a profit. |
Historical Data | Predicted Values | ||||
Month | Profit | Month | Profit | ||
1 | -£5,000 | 13 | -£2,226 | {=TREND(C41:C52,B41:B52,E41:E52)} | |
2 | -£4,800 | 14 | -£1,968 | The | |
3 | -£4,600 | 15 | -£1,709 | same | |
4 | -£4,750 | 16 | -£1,451 | function | |
5 | -£4,800 | 17 | -£1,193 | used | |
6 | -£4,500 | 18 | -£935 | in | |
7 | -£4,000 | 19 | -£676 | all | |
8 | -£3,800 | 20 | -£418 | cells | |
9 | -£3,300 | 21 | -£160 | as | |
10 | -£2,000 | 22 | £98 | an | |
11 | -£2,500 | 23 | £356 | array | |
12 | -£2,800 | 24 | £615 | formula | |
How To Enter An Array Formula |
Select all the cells where the array is required, such as F41 to F52. |
Type the formula such as =TREND(C41:C52,B41:B52,E41:E52), but do not press Enter. |
Hold the Ctrl+Shift keys down. |
Press Enter to enter the formula as an array. |
0 comments:
Post a Comment