Category Archives for "Analysis"

PowerPivot and Power View for Excel 2013

Analysis , Charts , Dashboards , Excel , Pivot Table and PowerPivot

If you are using PowerPivot for Excel 2010, then click here. The following collections of links and resources will help you go from short video overviews of PowerPivot and Power View capability to being fairly proficient. Video Overview of Excel 2013 PowerPivot and Power View Here’s a collection of short videos from Microsoft on Excel […]

Read More

Excel 2010 PowerPivot

Analysis , Charts , Dashboards , Excel , Pivot Table and PowerPivot

To learn more about PowerPivot and PowerView for Excel 2013, click here. If you have Excel 2010 you have access to Power Pivot, but not Power View. Just the capabilities of Power Pivot make it an extremely powerful tool that any power Excel user has to be familiar with. The ribbon has slight differences from […]

Read More

Smoothing Excel Chart Data with a Moving Average

Analysis , Charts , Dashboards , Excel , Formulas

Sales, marketing, and customer data can be so erratic that it’s hard to see patterns in your Excel chart or dashboard. With a couple of easy techniques and formulas you can smooth your data to make those hidden patterns visible. The easiest smoothing technique is the moving average. For example, instead of showing the current […]

Read More

Smoothing Excel Chart Data with Dynamic Smoothing

Analysis , Charts , Dashboards , Excel , Formulas

Smoothing Excel chart data with a fixed moving average works when you have experience with the data and how variable it is. But, if the data changes or its new you or management may want to experiment with a different number of periods in the smoothing average. In this example, an OFFSET formula is used […]

Read More

Dynamically Control Excel Chart Trend Lines with Check Boxes and Drop-Down Lists

Analysis , Charts , Dashboards , Excel , Formulas

You can get very creative using check boxes and drop down lists to give users the ability to view different sets of data and trend analysis. In this example the user can use a check box to select whether to show or hide the trend line. They can also use the drop down list to […]

Read More

Editing TREND and Array Functions

Analysis , Charts , Dashboards , Excel , Formulas

TREND and some other functions that work on an array (a collection) of cells are array formulas. They use an array of cells as data sources and usually output the results into an array of cells. Array formulas are entered by selecting the entire range to receive the resulting array, typing the formula, and then […]

Read More

Using the TREND Function to Forecast Future Values in Excel Charts and Dashboards

Analysis , Charts , Dashboards , Excel , Formulas

The TREND function will also forecast future points on the trend line. The TREND function format is, =TREND(known-y’s,[known_x’s],[new_x’s],[constant]) In the figure below the previous tutorial was used to fill D24:O24 with the array formula, {=TREND(D23:O23,D22:O22)} Use the TREND function to forecast out as many periods as you think are valid. In general, for most business […]

Read More

Using Excel’s TREND function to Create Trend Lines on Excel Charts or Dashboards

Analysis , Charts , Dashboards , Excel

To see the trend data and to create dynamic trend lines you can control with menus you need to use Excel’s TREND function. Using the Trendline function in the Analysis group is an easy way to add a trend line to a chart, but it doesn’t show you the data for that line and it […]

Read More

Correlation does not Imply Causation when Calculating Trend Lines

Analysis , Charts , Dashboards , Excel , Formulas , Metrics and Measures

So, just because you got a high R-squared on a trend does not mean you can do predictions with your trend line. It just means that the data trends in the same direction. You can get yourself, and your business, in trouble if you use a trend line for forecast and there is no causation […]

Read More

What is a Good R-Squared Value or Is the Fit Good for a Trend Line?

Analysis , Charts , Dashboards , Excel , Metrics and Measures

You’ve probably heard the rule of thumb that if R-squared is .7 or better then the trend line has a good fit to data and in general the higher R-squared is the better it is. However, there are several different types of correlation coefficients and each is appropriate under different conditions. A good approachable article […]

Read More