Spectral Analysis Chart – How to create one in Excel 2010

The lead time Spectral Analysis Chart, or lead time Histogram Chart is a very useful tool for visualizing and analyzing you process lead times. In this post I show you how you can create one in Microsoft Excel 2010.

Spectral Analysis Chart example

I’m using the same process data in this example as in the Control Chart example. There should be no problem to have both the Control Chart and the Spectral Analysis Chart in the same document. Step 1 through step 23 are therefore the same in both examples.

Let’s get started.

1. Create a new workbook

2. Rename “Sheet1” to “Data

CreateNewAndRename_thumb

3. Add the columns “Id” , ”Title”, “StartDate”, “EndDate”, “LeadTime”, “Type”, “Comments”

AddColumns_thumb

4. Select all the columns and the row below

5. Open the “Insert tab” in the Excel ribbon

InsertTable_thumb

6. Create a table by clicking on the “Table” button on the Excel ribbon

7. Check “My table has headers” and click OK

CreateTable_thumb

8. Rename table to “LeadTimeTable

9. Format the header row to make more useable

RenameTableAndFormat_thumb

10. Format the columns “StartDate” and “EndDate” as date columns

12. Add the formula “=[EndDate]-[StartDate]” in the “LeadTime” column

12. Format the “LeadTime” column as number column

LeadTimeFormula_thumb

13. Add some example data

ExampleData_thumb

14. Switch to the “Sheet2” worksheet and rename it to “Criterias

Now we will create criteria’s that will be used to select the appropriate rows from the “LeadTimeTable” on the “Data” sheet when we do the calculations for the control chart.

15. Add a label for your criteria and add it to the “Criterias” sheet

16. Copy the header row form the “Data” sheet as shown

FeatureCriteriasHeaders_thumb

17. Add the criteria to get all features by adding the formula =”=Feature” in the feature column

FeatureCriteriasFeatureFormula_thumb

18. Select the all the headers and the criteria row below

19. Open the “Insert tab” in the Excel ribbon

20. Create a table by clicking on the “Table” button on the Excel ribbon

21. Check “My table has headers” and click OK

CreateFeatureCriteraTable_thumb

22. Rename table to “FeatureCriteria1

RenameFeatureCriteraTable_thumb

23. Switch to the “Sheet3” worksheet and rename it to “Features

Feature Lead time selection formula

24. Add the column “Lead Times

25. In the “Lead Times” column add the formula =IF(LeadTimeTable[@Type] = “Feature”;LeadTimeTable[@LeadTime];””)

26. Select all the columns and the row with the formulas

27. Open the “Insert tab” in the Excel ribbon

28. Create a table by clicking on the “Table” button on the Excel ribbon

29. Check “My table has headers” and click OK

Create Feature LeadTime Table

30. Rename table to “FeatureLeadTimes

Rename to FeatureLeadTimes

31. Expand the table to the same number of row you have in “LeadTimeTable” table on the Data sheet.

NOTE! You need to expand this table as the data grows.

Expand Feature Lead time Table

32. Insert a new Sheet and rename it to “Features Calcs”

Insert Features Calcs sheet

33. Add the heading “Features count

34. In the cell below insert the count formula “=DCOUNT(LeadTimeTable[#All];4;FeatureCriteria1[#All])

Insert Features Count Formula

35. Insert a column heading with the name “Lead Times

36. Below the heading add numbers ascending from 1 to 30 (or what is appropriate in your data set)

Insert Lead time count

Now we come to the most trick part. Now we shall add an array formula.

How to enter an array formula in Excel
When you enter an array formula you need to:

  • Select the cell range the formula should be in
  • Write the formula without using Enter
  • Now finish the fomula by typing Ctrl + Shift + Enter

37.  Now select the the cells to the right of the Lead Times numbers

Select all cells

38. Enter the formula “=FREQUENCY(Features!A1:A14;’Features Calcs’!A5:A34)” without hitting enter.

Frequency calculation

39. Now press Ctrl + Shift + Enter.

The cell should now look like the picture below.

Ctrl shift Enter

At this point you have what you need to show a simple Spectral Analysis Chart/lead time Histogram Chart. But we will add some more useful data to the Chart.

Now we shall create a cumulative count to help us with our calculations.

40. Add the heading “Accumulated Count

41. In the first cell below add the formula “=B5

Accumulated Count Row 1

41. In the second row add the formula “=B6+C5

Accumulated Count Row 2

42. Now expand the second row formula down for the rest of the column.

Accumulated Count Fill

Now we will create some supporting calculations to determine at what lead time we have 75%, 85% and 95% of the features included. This information can be used to give predictions on when a new feature will be done based on historical data.

43. Add a the column heading “75”.

44. In the first row enter this formula “=IF(C5/$A$2 >= 0,75;A5;””)

75 percent formula

45. Now fill the rest of the column with that formula.

75 percent formula fill

46. Now repeat the step 43-45 for the 85 and 95 percent columns. Using the formulas “=IF(C5/$A$2 >= 0,85;A5;””)” and “=IF(C5/$A$2 >= 0,95;A5;””)

Add 85 and 95 percent formulas

Now we will calculate the lowest lead time that includes 75 percent of all the features.

47. Add the heading “75 Lead Time

48. In the cell below enter the formula “=MIN(D5:D34)

75 percent Lead Time

49. Now repeat this for the 85 and 95 percent as well.

85 and 95 percent Lead Time

Now we will calculate the max number of features at a specific lead time. We will use this when we draw the diagram later.

50. Add the heading “Max Count

51. In the cell below enter the formula “=MAX(B5:B34)

Max Count Formula

Now we will create three additional columns that will be the data source for the diagram to show the 75,85,95 percent marks.

52. Add the column heading “75%

53. In the first row enter the formula “=IF(D5=$D$2;$B$2;””)

54. Now fill the rest of the column with that formula.

75 percent serie formula

55. Add the column heading “85%

56. In the first row enter the formula “=IF(E5=$E$2;$B$2;””)”

57. Now fill the rest of the column with that formula.

85 percent serie formula

58. Add the column heading “95%

59. In the first row enter the formula “=IF(F5=$F$2;$B$2;””)”

60. Now fill the rest of the column with that formula.

95 percent serie formula

The result should look like this when you scroll down.

Percent series fill

Now it’s time to create the diagram. I use a 2-D Clustered Column Chart but you can also use a 2-D Line Chart.

70. Open the “Insert tab” in the Excel ribbon

71. Select the heading and all the rows in Count column

72. Insert a 2-D Clustered Column Chart.

Now you have a Spectral Analysis Chart/ Histogram Chart!

Insert Bar Chart

73. Move the chart to a new sheet and name it Features Histogram

Move Chart

Now it’s time to add the 75,85 and 95 percent marks.

74. Right click in the diagram and choose “Select Data…

Select Data Source

75. Click add and enter “=’Features Calcs’!$G$4” for the series name and “=’Features Calcs’!$G$5:$G$34” for the series values.

75 percent series added

76. Repeat step 75 for the 85 and 95 percent series as well.

All secies added

77. Click OK and we should be done! It should look like this.

Finished Histogram

Now you have a lead time Spectral Analysis Chart, or lead time Histogram Chart! Now you can replace the example data and add your own data. Just add a new row for every delivered feature.

This document needs some TLC as you add more data you need expand the number of rows in the different tables and and you will need to update some of the supporting calculations. It’s not that hard and will only take a minute or two.

You can also add other work item types and other criteria’s as you will find groups of work with different characteristics. And as mentioned before you can use the same base data as in the Control Chart example.

Download example file here: Histogram Example.xlsx

4 thoughts on “Spectral Analysis Chart – How to create one in Excel 2010

  1. Chris Achouiantz (@ChrisAch) 2012-11-07 / 15:17

    Thanks for sharing Håkan!
    It takes some effort to maintain, but the result looks great and gives a lot of food for thoughts.

    • Håkan Forss 2012-11-08 / 12:10

      Yes some TLC is needed but it is very easy to get going. Later on you could move the data in to some Data warehouse solution and automate the process.

      / Håkan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s