0
The Excel MATCH function returns an item’s relative position in supplied arrayand matches the specified value in the specified manner. The user can find the exact match for his specified value or can request for the closest match either above or below from the original value. Exceldummies.com recommendsusing MATCH function whenthe position of that particular item is required.
Syntax for Excel MATCH function
MATCH(lookup_value,lookup_array,match_type) where:
Lookup_value is the value you are looking for in the table.
Lookup_arrayis the data array which has to be searched. It should be an array reference or an array. In short, it is a range of cells that has to be looked for by the function for finding the match.
Match_type is an optional logical argument which can be set as 1, 0 or -1 as per the user’s requirement.
If the user has specified match_typeas 1, the Excel MATCH function will look for the closest match which is less than or equal to lookup_value. The Lookup_arrayshould be set in ascending order.
If the user has specifiedmatch_typeas 0, the Excel MATCH function will return an error after the search completion. For this match type, the lookup_ array needs not to be in any order.
If the user has specified the match_typeas -1, MATCH will find the closest match above the lookup_value. In order to use this option correctly, the lookup_array need to be arranged in descending order.
If the [match_type] argument is omitted it is assumed to be 1.
Thursday, 19 April 2012
Labels:
Excel Dummies,
Excel Match Function,
Excel Online Training
0
Formulas are type of equations which perform different calculations on the given values in worksheet. Creating formulas in Excel is easy once you have learnt the basics. Any Excel formula begins with an equal sign, as, (=).
Example =6+2*5
This formula multiplies 2 with 5 and adds 6 to the multiplication result.
How do Excel Formulas Work?
The Excel formulas work upon the same mathematical basics which everyone is well aware of since school days. For example for addition, you just need two numbers and the + operator or you can use the Excel syntax =SUM (number1,number2) which will add numbers in the given range of cells.
Different parts for creating formulas in Excel
Creating formulas in Excel make use of some basic but essential parts:
Functions: This is denoted by closed parenthesis like (). For example, the PI () function provides the pi value as 3.142...
References: For calculating equations, Excel uses the data given in cells. For every equation to be calculated, the cell works as variables and references as well. For Example A6 will return the value given in A6 cell.
Constants: The Constants in creating Excel formula are those text values/numbers which are entered directly ina formula.
Operators: Operators are just like simple mathematical operators’ symbols which define the type of calculation to be performed. For example:addition (+), subtraction (-) or multiplication (*).
Quickly copying formulas
While creating formulas in Excel you should understand that the similar formula can be entered speedily in a range of cell. You just need to select the range for calculation and type the formula over there. Next, press CTRL+Enter. When we copy any formulas, the reference gets adjusted accordingly.
Monday, 16 April 2012
Labels:
Excel Dummies,
Excel Formulas,
Online Excel Training
0
Forecasting is widely used in management and Excel allows us to solve simple forecasting tasks. Exponential smoothing is one of the most common techniques that is applied to time series data to make forecasts. It includes allpast observations and weighs recent observations much more heavily than older observations. Exceldummies.com teaches how this model is mainly used for smoothing and short-term forecasts.
Exponential Smoothing Formula
The equation to calculate an exponential smoothing is:
Ft= α At-1+ (1- α) Ft-1
where
Ft – Forecast for the period t.
At-1 Actual value of the time-series in the prior period,
Ft-1Forecast made for the prior period,
α is the smoothing factor, and 0 < α < 1
This method combines the previous forecast with a weighted error measurement. The error comes from the difference between the prior forecast and actual observation at the time of the forecast. A forecast is made by revising the current forecast. The errors of the earlier forecast are checked so that corrections can be made in the new forecast.
Check out Exceldummies.com for Exponential Smoothing tutorial with the real life examples and FREE resources.
Exponential Smoothing Formula
The equation to calculate an exponential smoothing is:
Ft= α At-1+ (1- α) Ft-1
where
Ft – Forecast for the period t.
At-1 Actual value of the time-series in the prior period,
Ft-1Forecast made for the prior period,
α is the smoothing factor, and 0 < α < 1
This method combines the previous forecast with a weighted error measurement. The error comes from the difference between the prior forecast and actual observation at the time of the forecast. A forecast is made by revising the current forecast. The errors of the earlier forecast are checked so that corrections can be made in the new forecast.
Check out Exceldummies.com for Exponential Smoothing tutorial with the real life examples and FREE resources.
Friday, 30 March 2012
Labels:
Exponential Smoothing Excel,
Sales Forecasting Excel
4
Excel’s linear trend analysis is a part of its regression analysis procedure. The regression analysis is used for identifying either a line or a curve that gives the most appropriate fit by using data points.The main idea behind this model is simply estimating the future values based on various kinds of trends or seasonal patterns in our data.
An equation for linear trend analysis can be put down as:
Yp= mX + b.
Yp: The predicted value for the variable which is dependent.
m: The slope of the regression line.
Y: intercept of the regression line is represented through b.
Easy Steps to Add a Trendlineto Your Chart in Excel
Linear trend analysis gives you the opportunity of displaying a linear trend line of a best fit line by using your available data. You can also see the best fit line equation and correlation efficient by using linear trend analysis. A trend line provides an idea about your sales that in which direction it is heading. Learn how to display a trend linewith exceldummies.comor follow these steps:
Click somewhere on your chart.
Go to the ribbon and click on layout.
Click on trendline further to the right.
Now select Linear trendline from options. A trendline will appear on your chart.
Exceldummies.com provides real life examples of Linear Trend Analysis.Visit exceldummies.com for self-paced Excel online video training.
An equation for linear trend analysis can be put down as:
Yp= mX + b.
Yp: The predicted value for the variable which is dependent.
m: The slope of the regression line.
Y: intercept of the regression line is represented through b.
Easy Steps to Add a Trendlineto Your Chart in Excel
Linear trend analysis gives you the opportunity of displaying a linear trend line of a best fit line by using your available data. You can also see the best fit line equation and correlation efficient by using linear trend analysis. A trend line provides an idea about your sales that in which direction it is heading. Learn how to display a trend linewith exceldummies.comor follow these steps:
Click somewhere on your chart.
Go to the ribbon and click on layout.
Click on trendline further to the right.
Now select Linear trendline from options. A trendline will appear on your chart.
Exceldummies.com provides real life examples of Linear Trend Analysis.Visit exceldummies.com for self-paced Excel online video training.
Wednesday, 28 March 2012
Labels:
Excel Dummies,
Linear Trend Analysis
0
Every business needs to have a proper future planning in order to grow and prosper in a good manner. Though none of us can have an idea about what lies in future, still making no efforts for future will only lead to a sure failure. Forecasting is used in various departments in the organization such as finance, sales, manufacturing and so forth.Creating forecastscan be very cumbersome process. Unless you have a computer software that automatically forecasts for you, Excel has great features allows you to forecast.
1. Traditional Methods: Traditional methods are the most effective methodologies of forecasting.
- Annual Forecasting: Variance is spread proportionally on a month to month basis for the remainder of the year.
- Monthly Forecasting: Current variances are assumed to be permanent and remain in the predicted figures.
- Quarterly Forecasting: Variance is spread proportionally within the quarters in the future.
- Run Rate Forecasting: Run rate is basically average and your data is divided by the number of periods.
2. Quantitative Methods: With Time Series Methodslooks at variable and collect the data from different time intervals.
- Moving Average: Model moves one period each time and it uses the most recent value.
- Exponential Smoothing: Exponential Smoothing is used for short term predictions. More weight is applied to most recent observations.
- Linear Trend Analysis: This model simply estimates the future values based on various kinds of trends or seasonal patterns in our data by using best-fit straight line.
When these calculations are done in the right way through right tools, the results are marvelous and accurateforecasts. Please visit Excel Dummies for to learn more about Forecasting Techniques and Data Analysis in Excel.
Tuesday, 28 February 2012
Labels:
Excel if Formula,
Excel Video Tutorial,
Excel vlookup Formula,
Help with Excel Spreadsheets,
Moving Average Sales Forecast,
Online Excel Training,
Pivot Tables Excel,
Sales Forecasting Excel
2
Although many people know how to use excel, not many people know how to use pivot tables. Pivot table is one of the most powerful tools when doing an analysis in Excel. Pivot Tables allow you to manipulate the data the way you want to see the results.
Pivot Tables allow users summarize large data, sort, count, filter items, switch rows and columns, drag and drop fields of the data which is stored in another source – another one table or spreadsheet anything you want. You can arrange the results based on the way you want to display. When you analyze large amounts of data, with Pivot Tables you can look at the same information in many different ways. How you organize and group the data often determines whether you find or overlook important trends.
Excel Dummies covers creating and using a Pivot Table to extract different information from one data sample.
The Steps that are covered are:
- Introduction to Pivot Tables
- Creating a Pivot Table
- Pivoting a Pivot Table
- Sorting and Filtering Data in Pivot Table
- Grouping Data in Pivot Table
- Calculations in Pivot Tables
- Updating a Pivot Table
- Pivot Table Charts
As an example, to see the steps for grouping data in all fields, or a single field, please watch the FREE Excel Pivot Table Grouping Data video tutorial by Excel Dummies. The tutorial teaches how to group the dates by month, and again, the grand totals are automatically calculated. Click here.
Excel Dummies tells you about several tips which will assist you in availing better usability and help with Pivot Tables.
If you’d like to learn more about Pivot Tables, please visit http://www.ExcelDummies.com for more information.
Monday, 27 February 2012
Labels:
Excel if Formula,
Excel Video Tutorial,
Excel vlookup Formula,
Help with Excel Spreadsheets,
Online Excel Training,
Pivot Tables Excel,
Sales Forecasting Excel