can anyone tell me how to use forcast,frequency funtion in excel pgm?

Answer 1

Excel’s FORECAST( ) function uses the best-fit line to forecast a predicted y value for a given x value. The syntax of the FORECAST( ) function is:

=FORECAST(x value, known y’s, known x’s)

where x is the data point for which you want a prediction. The known y’s are the dependent variables and known x’s are the independent variables.

Suppose you want to predict the y value for a x value of 140. The FORECAST function to perform this calculation is =FORECAST(140, B2:B11,A2:A11). Excel returns a value of 107.6.

You can use Excel’s Trendline feature to see a graphic representation of the best-fit regression line. To create a best-fit line, double-click the chart, select the series of data, and choose Add Trendline from the Chart menu. Select the Linear Option for a best-fit regression line. To display the slope and y intercept of the line, select the Options tab in the Trendline dialog box and click that feature.

The frequency function is useful to analyse a series of values and summarise them into a number of specified ranges. For example the heights of some children can be grouped in to four categories of [Less than 150cm]; [151 - 160cm]; [161 - 170cm]; [More than 170cm].

Note that this function does not analyse values into categories e.g. household expenditure into groups such as gas, electricity, water, rates etc. To perform this kind of analysis an Advanced Filter may be appropriate.

The frequency function has two arguments - the first is the range of cells containing values to be analysed; the second is the range of cells containing the upper values of each group banding. e.g. =FREQUENCY(A3:A120, B6:B10)

The second argument (the group upper limits) will exclude any values which exceed the highest category or banding. The function allows you to take account of this and extend the range of analysis to an additional category which contains all values that exceed the specified upper limit.