# Excel Hero Blog

## Calculate Pi by Throwing Hotdogs !

| 1 Comment | 0 TrackBacks
Recently I started teaching my 10 year old son about Pi, its relationship to Circles, how and what Pi can be used for and some methods to determine its value.

During this I stumbled on an article about How to calculate Pi by Throwing Frozen Hotdogs.
http://m.wikihow.com/Calculate-Pi-by-Throwing-Frozen-Hot-Dogs

This is more correctly known as the Buffon's Needle.

Buffon's Needle is a technique that uses the probability of throwing a needle onto a grid where the grid is spaced at the length of the needle. From this an estimate of Pi can be made.
There is mathematics behind this and I am not even going and try and explain why this works, but for those interested, you can read about it here.
http://en.wikipedia.org/wiki/Buffon%27s_needle

Obviously this was crying out for an Excel implementation.
I mean, "Who doesn't want to throw hotdogs with Excel!"

The Setup

The technique of estimating Pi by throwing hotdogs onto a grid involves marking out a grid on the floor with grid lines spaced equivalent to the length of the hotdog.

You then randomly throw hotdogs onto the grid and tally up the number of hotdogs that cross a grid line.

Pi is then estimated as the Total number of Hotdogs /Number of Hotdogs that cross a grid line/2.

In Excel, I originally thought of using a chart, but adding new series to a chart requires VBA.
I also wanted to be able to throw many thousands of hotdogs and most chart types have a limit of 255 series, although the number of points can be much greater than 255. I also needed the ability to rotate each hotdog individually.

I considered doing it all in VBA and drawing hotdogs on a UserForm, but that just seems too non-Excel.

I finally settled on using a Worksheet Range as the Grid, where I could obviously set the Row Spacing as the Hotdog's length.

I decided on a hotdog length of 50. This allowed for 10 Rows in the Grid and left room for user controls and result monitoring charts on a worksheet that would fit on most displays.

Excel doesn't have a Hotdog Drawing tool, but it does have a basic Drawing facility and so a straight line will suffice as a hotdog.
By using a Line I can use VBA to calculate its starting position and rotation as well as the color and line thickness, and then draw the hotdog.

It is a shame that although I can manually select a HotDog and change the Line Ends to be round, this functionality hasn't been included into the VBA Object Model and so we are left with straight lines with flat ends as hotdogs.
Maybe they have just been trimmed by the VBA Hotdog vendor?

Once a hotdog is thrown we need to accumulate some statistics.

Simplistically I only need to know the number of hotdogs that cross/touch a grid line and that could be simply accumulated as each throw occurred.

But once I started I couldn't help myself and added the history and cumulative Pi value and variance from Pi as charts. I also colored the hotdogs according to there location Red for Hotdogs that Crossed a grid line and Green Hotdogs for those that missed a gridline.

The remainder of this post will examine the techniques used for each section.

Simulation
The basic of the simulation are as follows
1. Setup Grid
2. Throw a random Hotdog
a. Choose a random centroid X, Y and Rotation for a Hotdog
b. Calculate X & Y position of each end
c. Check if the Hotdog intercepts a line
d. Draw the HotDog
e. Record the throws status
f. Tally results
3. Repeat step 2
4. Calculate Pi

Of course this could all be done in memory. There is no need to display the hotdogs. Displaying them will definitely slow down the process. But wouldn't that be boring !

Setup a Grid

Excel uses an orthogonal grid starting at 0,0 at the Top Left corner of the A1 Cell. X values increase to the right and Y values increase down the worksheet.

I originally decided to have grid of 10 rows of size 50pts, which is 500pts. This allows for space above/below for inputs, controls and stats. I was going to use a Hotdog length of 50.

But in Excel 2010 using a standard Calibri Font, I could only get an odd 49.5 pts Row Spacing (66 pixels). Close enough and I can use that as a variable in VBA when I check the hotdogs location, more on this later.

Setup Code
To ensure the grid is correctly calibrated a small piece of VBA is used to setup the Row sizes

It only need to be used once on a PC or after people change the view zoom or row /column sizes etc, just to make sure everything is setup correctly.

Sub Setup()
Const HotDogLength As Double = 49.5

Worksheets("Throw").Select
Range("A1").Select
Application.ScreenUpdating = False

'Cleanup old hotdogs
CleanUp

'Setup the Throw Sheet
Columns("A:A").ColumnWidth = 1.43
Columns("B:K").ColumnWidth = 10
Rows("1:5").RowHeight = 19.5
Rows("6:17").RowHeight = HotDogLength
Rows("18:1048576").EntireRow.Hidden = True

With Range("B6:K15")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 2
.Weight = xlMedium
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 2
.Weight = xlMedium
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 2
.Weight = xlMedium
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 2
.Weight = xlMedium
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ThemeColor = 1
.Weight = xlMedium
End With
End With

Range("Runs") = 200
Range("Runs").Select
Application.ScreenUpdating = True

End Sub
The Column sizes aren't important but from an aesthetic point of view it looks better if the hotdogs are within a defined area.

Only the Hotdogs Y value is required or used for checking the hotdogs location, This will be discussed later.

The Main Simulation

The Main VBA code runs through the steps of the simulation as described above

2. Throw a random Hotdog
a. Choose a random centroid X, Y and Rotation for a Hotdog
b. Calculate X & Y position of each end
c. Check if the Hotdog intercepts a line
d. Draw the HotDog
e. Record the throws status
f. Tally results

a. Choose a random centroid X & Y position and Rotation for a Hotdog

For each throw of a hotdog we need to calculate a random X, Y coordinate of the centroid of the hotdog and a random rotation of the hotdog between 0 and 2 x Pi Radians

'Calc start pos

Randomize
xc = XOffset + Rnd() * 10 * HotDogLength
yc = YOffset + 10 * HotDogLength * Rnd()
Rot = Rnd() * 2 * Pi

b. Calculate X & Y position of each end

From the Centroid, Rotation and Hotdog length we can calculate the end position of each hotdog using simple trigonometry.

'Calc end positions
x1 = xc + Cos(Rot + Pi) * HotDogLength / 2
y1 = yc + Sin(Rot + Pi) * HotDogLength / 2
x2 = xc + Cos(Rot) * HotDogLength / 2
y2 = yc + Sin(Rot) * HotDogLength / 2

c. Check if the Hotdog crosses a gridline

To check if a Hotdog crosses a grid line it is simply a matter of determining which section of the grid each end is in. If they are in different sections the hotdog crosses a line.
This is easily achieved by calculating the Integer value of the hotdogs two end Y values divided by the Grid Spacing. The YOffset is first subtracted to normalise the data .

If Int((y1 - YOffset) / HotDogLength) = Int((y2 - YOffset) / HotDogLength) Then
Worksheets("Results").Cells(same, 1) = i
HotdogColor = RGB(0, 255, 0) 'Green
Worksheets("Results").Cells(i + 1, 6) = -10
same = same + 1
Else
Worksheets("Results").Cells(cross, 2) = i
HotdogColor = RGB(255, 0, 0) 'Red
Worksheets("Results").Cells(i + 1, 6) = 10
cross = cross + 1
End If

d. Draw the Hotdog

Once the code has determined if the Hotdog is a Red(Crosses a gridline) or Green (Is between the gridlines) we can draw the hotdog. The drawing is outsourced to a small subroutine to simplify the code flow.

Sub DrawDogs(x1, y1, x2, y2, HotdogColor)
'Draw Hotdog

With Selection.ShapeRange.Line
.Visible = msoTrue
.Weight = 6
'.capType = msoRound ??? come on MS Guys complete the Object Model
.ForeColor.RGB = HotdogColor
End With
Application.ScreenUpdating = True
End Sub

The function is called by simply calling the DrawDogs subroutine which requires the X & Y values for each end of the dog as well as the Hotdogs color

Call DrawDogs(x1, y1, x2, y2, HotdogColor)

e. Tally results

To calculate Pi using this technique we simply need the tally of the Number of Hotdogs that cross a Gridline and the Total thrown

I decided early on in the design of the workbook that keeping records of which throw was a Cross or Miss may be useful and so 2 columns are setup to store these.

All results are stored on the Results sheet

Columns A & B keep a record of which throw is which type of throw

Column E stores the running Pi and Column F is used for the display of the individual

Display Results

6 measures are monitored on a throw by throw basis

1. The individual hotdog status as Red (touches a gridline) or Green (sits between gridlines)
2. The Throw No.
3. The Current estimate of Pi
4. The Historical Estimate of Pi vs Pi
5. The Variance from Pi as a percentage of Pi
6. The historical throw Status of each throw as either Cross or Not-Crossed

A number of Named Formula are used to convert the data to a suitable format for charting.
The Named Formula are then used directly in the various charts.

These include:

 Worksheet Storage Names Runs =Throw!\$D\$2 Iteration =Throw!\$H\$2 Summary Names Cross =COUNT(OFFSET(Results!\$B\$2,,,COUNTA(Results!\$B:\$B)-1,1)) Same =COUNT(OFFSET(Results!\$A\$2,,,COUNTA(Results!\$A:\$A)-1,1)) Calculated Names i =OFFSET(Results!\$D\$1,1,,COUNTA(Results!\$D:\$D)-1,1) PiEst =OFFSET(i,,1) Ind =OFFSET(i,,2) PiAct =PiEst*0+PI() PiVar =(PiEst-PI())/PI()

The above Named Formula are used to transfer the results into the charts on the Throw Worksheet.

Remove Hotdogs

Prior to a simulation run it is good practice to remove the existing hotdogs from the floor and so it is in the Excel implementation.

This is achieved by using a small piece of VBA code.

Sub CleanUp()
Dim Shp As Shape

Worksheets("Throw").Select
For Each Shp In ActiveSheet.Shapes
If Left(Shp.Name, 6) <> "Button" And Left(Shp.Name, 6) <> "Chart " Then
Shp.Delete
End If
Next Shp

Worksheets("Results").Range("A2:B10000").ClearContents
Worksheets("Results").Range("D3:F10000").ClearContents
Worksheets("Results").Range("F2").ClearContents

[Iteration] = 0
[Iteration].Select

End Sub
The code loops through the shapes in the Shapes collection and if not named "Chart" or "Button" deletes it
It also resets the Results sheet.

this file which has only been tested in excel 2010 and 2013 from Download File

Closing

I hope you enjoy this implementation in Excel of the Buffon's Needle provided by Hui, proving that indeed Excel can throw Frozen Hotdogs with the best of them.

I have ran this many times with up to 10,000 hotdogs without issue, however from time to time I have experienced an  issue where Chart 3 becomes invisible. If this happens simply close the file without saving and re-open it.
If you work out why please let me know.

Hui is a Consulting Mining Engineer by profession and guest author at ExcelHero Blog and Chandoo.org.
Hui received a Microsoft MVP award (Excel) in 2013 and 2014.

Hui has published over 100 posts about all things Excel at:

@ ExcelHero Blog

Motion Induced Blindness

Lilac Chaser

Counter-Intuitive Illusory Contours

M. C. Escher

Two Sinusoids

Cafe Wall

@ Chandoo.org

## Master Excel PowerPivot Now

Outside of VBA, Excel's single most powerful feature is PowerPivot, which is an official MS add-in for Excel that takes pivoting not just to the next level, but to a whole other world.

With PowerPivot you can connect to your enterprise databases, analyze data, create advanced reports & prepare dashboards right from familiar interface of Excel.

You should learn as much as you can about PowerPivot if you are looking for ways to enhance your skills in data analysis, pivoting & reporting areas. To do so, you'll need Excel 2010 or newer.

This is a big subject and if you want to get up to speed quickly and learn the secrets of how best to capitalize on PowerPivot my recommendation is to take Chandoo's new PowerPivot course

If you are familiar with Excel and PivotTables and you want to take your career to the next level then it's time to learn PowerPivot now

.

## Back By Popular Demand - The Awesome Excel Dashboards Course!

| 1 Comment | 0 TrackBacks

Mynda's Excel Dashboard Course is open once again and right now she is offering a 20% Discount Code:  DASH

That code is valid until the end of the day on January 30.

You may recall that last summer Mynda offered this course for the first time and it was an absolutely smashing success. Mynda Treacy has put together a truly fantastic Excel Dashboard Course

Dashboards are the new buzzword when it comes to Excel reporting and employers are specifically asking for these skills.

If you need to take your Excel skills to the next level to get that dream job, then knowing Excel Dashboards will help you stand out from the competition.

Dashboarding is an incredibly valuable tool in today's market for consultants, analysts, and managers.  But Excel surely does not make it straightforward to build highly professional and interactive dashboards.  That's why this type of training is crucial.

The course is video based, delivered online and is available 24/7. You also receive comprehensive workbooks and sample dashboards to keep. There's even an option to download the videos.

The previous classes have been a huge hit with many saying that they love the cool techniques and that they've been able to impress their colleagues by using them in all sorts of reports, not just dashboards.

Click here to find out details of the course, read comments from past students, and watch the 'behind the scenes' video that shows you what you'll receive as a student.

BONUS

If you sign up by the 30th of January you can get it for 20% off.

I am really happy that Mynda and her crew have put this Excel Dashboard Course together.

There are many techniques in this course that will surprise you, and you will immediately be more productive.

Do yourself a favor and read this page.  The course is awesome.  The price is incredibly fair.  And for the next six days, you can enroll with a 20% discount.

Consider this: although I get approached by countless people to promote their Excel related products, I rarely do.  I must consider it awesome first and foremost, and it must be extraordinarily valuable to you.  The only Excel training products I have ever recommended are my own course (EHA), Chandoo's courses, and now Mynda's Dashboard Course.  Think about that.

## Regional Excel Differences - Leave Your Mark

My friends in Europe (Italy and Hungary) are on a mission to collect some frequently used regional settings from different languages and versions of Excel.

I would like to support their effort by drawing your attention to this project. Excel comes in many different language versions.  I only use the English version of Excel, but I have seen first hand how trivial differences between the versions can cause differences in how formulas work.

Complicating matters, we can change the regional settings of Windows - which adds another layer of nuanced differences. For example a common problem are the different separator characters - these are dependent on both the regional settings and the language and version of Excel too.

In most cases the average Excel user does not realize what is happening inside Excel, but as Excel developers we must keep in mind the challenges presented by these differences - especially when we are not 100% sure what local settings will be used by our end users.

I have already sent the English settings of my Excel 2010 and 2013. Now it is your turn - if you use a local-language version of Excel, please take a look at this post, where you can find how you can contribute to this important project - it takes just 3 minutes!

I am really interested to see the final collection and read the experiences collected!

Thank you!

The Excel Hero Academy opened registration yesterday for the 4th class ever. And just like that the enrollments came flying in!  Several hundred so far.

The quality of each of the classes in terms of the students in truly amazing and this class, EHA4 is no different.  I know many of you have been waiting for an open enrollment.  This is it.  The next one won't be until the Spring of 2013 most likely.

EHA is truly an amazing journey.  I break down Excel into concepts that most experts have never considered and by the time I'm done weaving it all back together,  you are left with that feeling of Eureka... and think to yourself, of course that's how Excel works!

Hundreds of alumni will be retaking the course and are very happy to do be doing so because there is just so much information that sometimes viewing a particular lesson a dozen times reveals new actionable insights each time.

How does it work?

The course is actually a hybrid of work-on-your-own-schedule and a preset schedule.  Allow me to explain...

All of the materials and resources for the course are online.  However, they are not presented as a webcast.  Rather, the videos play on demand, 24/7, when it is convenient for you. The modules come out on a weekly schedule.  So during week 1, all of the students work on the materials from Module 1 whenever it is convenient for them, 24/7.

There is massive interaction in the class forums discussing the concepts and their application.  Homework is assigned for every module and students are encouraged to post their completed assignments (usually workbooks) to the module's HOMEWORK WALL.  This is an extraordinarily beneficial activity, as everyone learns from everyone else.

It's really amazing.

So then on to week 2 and Module 2 is released.  The same activity occurs 24/7 during that week, but this time on the Module 2 lessons and homework.

Week 3 brings Module 3, and the same activities.

Now we take a break for a week so that students can review all that they've learned from the first three modules. And trust me, you will need this time as the concepts are very powerful and take time to fully penetrate.  We do some challenges during this time as well.

This pattern of three modules followed by a review week is followed throughout the duration of the rest of the course.

So we are on a schedule to do three modules every four weeks, but you decide when you will study throughout the process.

Are you ready to discover the next level of Excel?

The value proposition of EHA is off the charts.  You will learn in 16 short weeks and for a few hundred dollars what it took me decades to learn.  The value of the course is that it dramatically shortens your learning curve.  Master Excel on one side, and command VBA on the other.  With EHA you will become an Excel Hero.

And for the first time Chandoo and I are teaming up to offer our courses together, at a discount.  Imagine that the EHA Super Bundle has my entire course as well as Chandoo's Excel School & Dashboards Course. That's more than 50 hours of Excel and VBA training, and more than 60 example workbooks and tools.

Master Excel Now by enrolling in EHA4.

Testimonials

If you've spent any time on this blog, you know the types of potent Excel solutions I share.  Well, it took me two years to fully develop EHA. I'm telling you... you should take the course.  It's that simple.

Here's what students have to say:

• "Jaw-dropping... For the advanced Excel user, Daniel's Blog and the Excel Hero Academy are
treasure chests full to the brim with invaluable Excel information, techniques and ideas." - Robert Mundigl

• "Daniel's expertise in Excel is unrivaled in my opinion... Daniel's Excel training has literally changed the way I view Excel and use it for business purposes." - Vedantham Srinivas

• "I was especially impressed with the Excel-Access set of tools that enables Excel to used to retrieve data from and update an Access database." - Dennis Plum

• "I challenge 'anyone' to put together a better course than EHA for a comparable cost. I'll go out on a limb here say, it aint gonna happen." - Calvin LeBlanc

• "I've never seen anything like Daniel's course. Nothing is remotely like it. Daniel's course has paid for itself time and time again." - Michael Shallal

• "History will show that Daniel Ferry has built a fabulous body of work." - Brendan Drew

• "There is nothing like Daniel's course out there. The content is like discovering gold." - John Hackwood

• "This is an empowerment system with so many extras. Thank you, thank you for a fabulous Excel journey and experience." - DaniÃ¨le Questiaux

• "I am very very happy with the course, best I have ever invested in. I am glad I ordered the download option as I believe I will keep learning from your modules for a long time to come." - Ajay Gajree

• "The content and videos in Daniel's class are the best I have seen." - Wanda Norrick

• "Forget all the businessy stuff. Daniel is to Excel as Neo is to The Matrix. By the end of the course, you'll be 'walking the path' with your new powers of Excel-fu. Learn from The One!" - Mark Sessoms

Master Excel Now by enrolling in EHA4.

But hurry.  The next class is next year.

.

## Awesome Excel Dashboard Course

The next class of the Excel Hero Academy will open in about a month (mid September, 2012)!

I am thrilled that there are already several hundred students on the waiting list, and many of them have been waiting since February.

In the meantime I have recently learned that Mynda Treacy has put together a truly fantastic Excel Dashboard Course. She was kind enough to allow me to review several of the videos, and I can report that her My Online Training Hub has put together a truly first-rate course.

Dashboards are the new buzzword when it comes to Excel reporting and employers are specifically asking for these skills.

If you need to take your Excel skills to the next level to get that dream job, then knowing Excel Dashboards will help you stand out from the competition.

Dashboarding is an incredibly valuable tool in today's market for consultants, analysts, and managers.  But Excel surely does not make it straightforward to build highly professional and interactive dashboards.  That's why this type of training is crucial.

The course is video based, delivered online and is available 24/7. You also receive comprehensive workbooks and sample dashboards to keep. There's even an option to download the videos.

The previous classes have been a huge hit with many saying that they love the cool techniques and that they've been able to impress their colleagues by using them in all sorts of reports, not just dashboards.

Click here to find out details of the course, read comments from past students, and watch the 'behind the scenes' video that shows you what you'll receive as a student.

BONUS

If you sign up by the 7th of August you can get it for 20% off, and if you do, I will be happy to offer you a special \$150 discount on Excel Hero Academy 4 next month. Just email me your receipt!

I am really happy that Mynda and her crew have put this Excel Dashboard Course together.

There are many techniques in this course that will surprise you, and you will immediately be more productive.

Do yourself a favor and read this page.  The course is awesome.  The price is incredibly fair.  And for the next six days, you can enroll with a 20% discount, and save \$150 off of EHA4, next month.

Consider this: although I get approached by countless people to promote their Excel related products, I rarely do.  I must consider it awesome first and foremost, and it must be extraordinarily valuable to you.  The only Excel training products I have ever recommended are my own course (EHA), Chandoo's courses, and now Mynda's Dashboard Course.  Think about that.

## Excel Formulas Can Make a Graph

| 1 Comment | 0 TrackBacks

If you have followed my work for any period of time, you know that I'm a big advocate of using named formulas to do the heavy lifting in my models and my dynamic charts.

Today I'd like to share with you a very fine example of this by the Frankens Team.

I love this chart, but whether you love it or hate it, the techniques behind how it works are fascinating!

A big thanks to the Frankens Team for writing this article and sharing it with the Excel Hero Community!

Infographic with Excel

picture 1

picture 2

Can Excel be a creative tool to create advanced, infographic-styled charts? Our answer is YES!

We created some interesting charts combining different chart types and using the built-in features in a bit of an unusual way.

In this article we would like to show how to create the chart you can see above, and how you  can make it dynamic, based on the cell selected by the user.

The data we use on the chart comes from this infographic:
http://awesome.good.is/transparency/web/1010/political-climate-chart/interactive.html

The numbers are percentages representing the fraction of the people with regard to the particular issue that is the top priority.

We designed a chart a similar to the original one, because the circle format could visualize this kind of data  more effectively than a linear one. All of the 20 measured  issues (categories) are visible on the chart now, but you can add more data to the table. The names in the file are built to be dynamically growing, so the new data will immediately appear on the chart.

To make the comparison easier we turned the chart inside out and used active-cell based highlighting together with a marker line.

## Chart components

On the chart you will find 4 data series, three of which are filled with the radar type chart, and the fourth one is a doughnut chart with a  label. The dynamic chart title serves to make the chart more "readable".

It is important to note that nothing is drawn manually.  The only pain is that Excel cannot set the angle of the label added with the help of the doughnut series, so it is adjusted manually.

• The white columns growing from outside to the centre of the circle represent the data. But the series itself is the green area - it "cuts out" the data columns from the white background of the radar chart.
• The black outline around a white column highlights the data selected by the active cell (row)
• The grey dotted circle is a contour-line and it indicates the value of the category to make comparable with the others.

Doughnut data series:
• The black circle at the outer rim was created as doughnut-type which makes creating the labels easier.

## Primary data display - inside-out columns

picture 3

picture 4

The whole chart is inside-out, so we need the data for series as 1 - <percentage value>. The gridlines may help to find out what is happening - you can see by counting on picture 4 that there are 8 data points for each category value (data column) plus one for the gap between the columns. So the total number of data points = 9 * number of categories.

The array for the green data series should look something like this: (remember, the data here is 1 - <percentage value>)

1

0.25

0.25

0.25

0.25

0.25

0.25

0.25

0.25

1

0.39

0.39

0.39

0.39

0.39

0.39

0.39

0.39

1

0.26

0.26

.

.

.

How can this array been created as a named formula - without using helper cells on the worksheet?

## Capturing input data

First we define the parameters of the data series:
 Named formula Note Refers to n_ColPoints Predefined number of data points for one category + 1 point for the gap =9 n_Category Number of categories in the data table. In the example n_Category = 20 =COUNTA( Sheet1!\$A\$1:\$A\$100 ) n_TotPoints Total number of data points on the radar chartIn the example n_TotPoints = 180 =n_ColPoints * n_Category

You can change the value of n_ColPoints - lowering it will result in a more triangular-shaped column, increasing will diminish the gap. In the example we use 9, as this gives a good result regarding visibility.

Note: Number of categories are limited to 100 in the dynamic named formulae, however the number of effectively displayable categories is far less.

Create a dynamic range for the input data:
 rng_h n_Category could be used in the INDEX formula to make the data table dynamic =Sheet1!\$B\$2:INDEX( Sheet1!\$B\$2:\$B\$100, n_Category)

## Building the main data series

And here comes a bit more difficult part...

The structure of the array we would like to have is simple: a gap data followed by the 1st category data repeated 8 times, gap again, 2nd category data repeated 8 times, gap, etc.

How can we imagine it?

## Step 1 - Repeated values for the chart columns

First of all, let's see that if we can separate the problem of category data from the problem of gap points! If we build up an array without gaps but having 9 times the category data, it will not be a big deal to change the 1st, 10th, 19th, 18th, 37th,...,172nd data to the gap-value.
So let's start with this array as intermediate result - every value listed 9 times, and there is 20 category values, so we need an array of 180 elements:

0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.74

0.74

0.74

.

.

.

And what do we have as input?

The name rng_h contains the input data as a vector of 20 category values:

0,75

0,61

0,74

.

.

.

So we want to have a one-dimensional vector of 180 elements using another one-dimensional vector with 20 elements and we need to repeat each element nine times. A very powerful function of Excel could be used in this case: MMULT ! What is the array we should multiply by our rng_h to have the above mentioned result? If you are familiar with the rules of matrix multiplication, you will easily find the answer:

## Step 2 - Build a helper matrix for the value selection

The next step is to find a way to build up this 180 x 20 sized matrix of 1/0 values. It is simple if you see the pattern: 1 = TRUE, 0 = FALSE, so this matrix is a 2D representation of which data point belongs to which category in the 180 length array:

## Step 3 - Build a helper array for the value addressing

Now we are close! We need the left-hand-side vector from the above picture which tells us which category the data point belongs to. It consists of 180 elements: repeating number 1 nine times, number 2 nine times, going up to 20 repeated nine times. We can create it easily using the INT formula!

Let's summarize what we have now!

We will need two helping-vectors:

 arr_pnt row-sequence to number the data points from 1 to n_TotPoints {1;2;3;....;180} =ROW( Sheet1!\$A\$1: INDEX(Sheet1!\$A: \$A,n_TotPoints)) arr_cat column-sequence to number the categories from 1 to n_Category {1,2.3,.....,20} =COLUMN( OFFSET(Sheet1!\$A\$1,,,,n_Category))

Determine the structure of the data points:
 arr_pnt_cat this array - as a sequence - identifies which category the data point belongs to. It consists of 180 numbers, repeating 9 times each number from 1 to n_Category: {1;1,1;1;1;1;1;1;1;2;2;2........;20;20} =INT( (arr_pnt-1) / n_ColPoints )+1or an other solution:=MATCH( arr_pnt, arr_gap, 1 )(see arr_gap below)

So here is the formula for the 1/0 matrix from the first picture, with double negation to convert the TRUE-FALSE to 1/0: --(arr_pnt_cat=arr_cat)

And the matrix multiplication will create us the array without gaps:
MMULT(--(arr_pnt_cat=arr_cat), rng_h)

## Step 4 - Inserting gap values

To create the gap values, we need to change the 1st, 2nd...172nd value of this array to 0. The idea is this: if the sequence number of the data point (arr_pnt) is an element of the set of gaps then it should be 0. So we will need the set of gaps:
 arr_gap sequence number of data points used to separate the categories (the gap) {1;10,19;28;37;.....;172} =(ROW(rng_h)- MIN(ROW(rng_h)))*n_ColPoints+1

With a MATCH and ISERROR we can have the 0s for the gap points:
ISERROR(MATCH(arr_pnt, arr_gap, 0)

One minus this whole array turns the series inside out :-) So here is the final named formula for the main data series!

 arr_01 Data series representing the inside-out columns. Repeats 1 minus data percentage 8 times alternating gap points with value 1. =1-MMULT(--(arr_pnt_cat=arr_cat),rng_h)*ISERROR(MATCH(arr_pnt,arr_gap,0))

After creating a filled radar chart adding arr_01 to data series you should see something like on picture 3. Please change the maximum of the axis to 1,1.  The minimum should be 0.

## Category labels

Now we have a static inside-out chart. The easiest way to add data labels is by using a doughnut chart with number of slices equal to the number of categories. We will need a very simple array as data series for this doughnut consists of 1s for each category. Power of 0 would not be a solution in this case, because Excel interprets 0^0 as #NUM error, so if we have 0 value or missing data in rng_h, the doughnut ring chart will not be in line with the columns - that is the reason why we use ISNUMBER here, and *1 converts the boolean values to numbers.
 label_1 ISNUMBER for the data range rng_h gives back TRUE where we have data. You need to multiply by 1 to convert it to numeric values: {1;1,1;...;1} =ISNUMBER(rng_h)*1

Add label_1 to the chart series, change the chart type to Doughnut and set the hole size to maximum.

Create this named range for labels:
 labels Dynamic range for labels =OFFSET(rng_h,,-1)

And insert it to the series formula of the ring via the formula bar:
=SERIES( "label_1",

Now you can add labels to the chart, you need to change it to show the category names. Unfortunately Excel can not adjust the angle of the labels according to the slices - the labels must be rotated manually.

## Active-cell based highlight

Excel's CELL formula without the second parameter makes it possible to determine the active cell. (You can read more about it here.)

 active_row The formula gives back the row number within the data table. =CELL("row")- CELL("row",rng_h)+1

Now we build up the black outline highlighting the data of the active category. We do not take care of the rest of the categories, so all these data must be zero. We use only the part of the data point-category array (arr_pnt_cat) where the numbers are equal to the number of the active category:
 arr_act_cat This array contains 0s for all the categories except the active row-category (9 times 1). =(arr_pnt_cat=active_row) * arr_pnt_cat

With exactly the same logic used to build up the main data series we can create the data series for the black outline:
 arr_a1 Comparing to arr_01 the only difference is in the 1-0 matrix: here we use the active-category based arr_act_cat instead of arr_pnt_cat which contains all of the indexes. =1-MMULT(--(arr_act_cat=arr_cat),rng_h)* ISERROR(MATCH(arr_pnt,arr_gap,0))

Add the arr_a1 series to the chart. It should be a filled radar chart. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour.

Active-cell based circle

This circle represents the value of the active category and makes the comparison easier . The only thing to do is to set up a series that contains this value for all of the 180 data points.
 arr_a2 Choose the active data from the data table using INDEX and create a vector of this data by multiplying with a 1-vector. If the active row is not in the data table, we do not need this circle, this is why multiplied with is_in_rng =(1 - arr_pnt ^ 0* INDEX(rng_h,active_row))* is_in_rng

Where is_in_rng is:
 is_in_rng We have chosen this nice formula to determine if the active row is within the data range or not. If the active row is below the table, MEDIAN gives back the last row of the table, so the equation will be false. Similarly when the active row is above the table MEDIAN gives the first row of rng_h (which is CELL("row",rng_h) so the equation will be false too. =CELL("row")= MEDIAN( CELL("row"), CELL("row",rng_h), MAX(ROW(rng_h)))

Add the series arr_a2 to the chart. It should be filled radar. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour and dotted style.

Because cell selection does not trigger calculation, you need to add a one-liner code to the worksheet object. In the VB Editor click on the sheet name under your project, and copy this code to the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Parent.Calculate

End Sub

It tells Excel to calculate the worksheet for each and every selection change.

## Dynamic Title

Only one thing left: to make the chart title dynamic showing the name and value from the active row.

 TT This name will be added to the chart title. The formula itself is a concatenation on the active data, the name of active category and some line breaks CHAR(10). =IF(is_in_rng,TEXT(OFFSET(OFFSET(Sheet1!\$A\$1,active_row,),,1),"0%")& CHAR(10) & "of Democrats viewed" & CHAR(10) & LOWER(OFFSET(Sheet1!\$A\$1,active_row,)) & CHAR(10) & "as a top priority" & CHAR(10)&"in 2011","")

On the formula bar assign the name TT to the chart title box.

And you can find some more charts with this technique here:

by Frankens Team
With special thanks to Suzanne Laing for the language corrections!

http://www.excelhero.com/blog/2011/05/excel-formulas.html

If you like the Excel formulas information and other powerful techniques shared here on the Excel Hero blog, please sign up for our newsletter:

## One Year Anniversary - Excel Hero LinkedIn Group

I am very happy to publish this guest article about the amazing first year of the Excel Hero LinkedIn Group from Krisztina SzabÃ³. Thank you for writing it Kris!

---

One year ago, this was the beginning of Excel Hero Linkedin group:

Welcome to the Excel Hero Linkedin Group!
Let's make this The Place for Excel answers, sharing, and networking. So what's on your mind?

What happened in the past one year?

Now we have almost 1800 members. That is a huge number. Only 1% of Linkedin groups have 1000+ members! Regarding Excel-related Linkedin Groups we are not the largest, but I am sure I can say we are one of the best! Why? Because we have lot of interesting discussions, we posted some really good challenges and shared knowledge and helped to each other.

A few weeks ago Daniel opened the group - this turning point was an important change in the life of the group.

Let us summarize the most interesting threads from the period we were a closed group!

We had 123 discussions and 830 comments posted. 220 members commented - good work!

We are very proud that we had 53 discussions asking for help in very different topics - mainly in connection with formulas - and almost all of the setters had great solutions to the their problem.

28 discussions were started to share knowledge - you posted several great links to your work, articles and other training materials. Just some examples: we received fantastic active cell-based dynamic charts, great non-VBA solutions for barcodes and text related Pivot tables, useful add-ins to help work with chart labels. You collected the most important excel add-ins in a discussion started by Chun Lee. 19 of you posted comments there, with very different add-ins according to your needs.

You were talking about some general topics questions (issues?). An interesting one was about the Non-accepted knowledge discussed by Kris - Krisztina SzabÃ³: What can we do when company/boss/colleagues afraid of our knowledge? The outcome of the discussion was:
• "Tease out the methods into simple steps so that it presents in a very understandable way" (Don Love) A great book was also recommended by Don and others: Professional Excel Development, 2nd Ed. by Bovey, Walentin, Bullen and Green
• And an advice for outside consultants: involve employees of the company, educate them the most important or common areas (Zack Barresse)

The most popular discussion was the Awesome Formula Challenge! where we tried to convert numbers into written text. We had 83 comments on this discussion from 22 members! Daniel started the discussion with a 1371 character-long formula - it was exciting to see how some experts of you challenged, changed and shortened it. The most important enhancements were posted by our formula-savant, Roberto Mensa. The result of this fantastic "team" development process was that we were able to drop the formula below 600 characters! Amazing!

Another great, long-life discussion was the one with the title: What is the Best Excel Tip You Can Share With Us? 41 members shared tips with us in 66 comments. We had many different tips from VBA-codes to regular expressions, from tricky formulas to practical keyboard shortcuts. Plus some effective advices for project teams, like this very important one: "ALWAYS think about the end user." by Oz du Soleil.

Many of us use non-English Excel and/or have non-English local settings in the operating system.  Some questions focused on the problems coming from these kind of settings and difficulties in VBA. Daniel gave us a great tip to use the arguments of Application.International to find out which separator characters are used in a particular computer. If you develop for international use, good to memorize it!

The last discussion of 2011 was posted by Roberto Mensa on new year's eve: Countdown starts .... Useless Functions.... You don't want to see in 2012! 77 great comments were posted by 23 of you. There were great debates around the necessity of CONCATENATE - good reason that sometimes it is more convenient to type the list separator character then typing &.

We had an interesting discussion around the INT-ROUNDDOWN-TRUNC formulas. It is possible to use one with some calculations to substitute the other. Don Love stated an essential point when using formulas: these formulas are "important to keep because they document your work." - because the formula name has a meaning!

The story of CHOOSE is also interesting - some of you (experts) never used, meanwhile some think it is essential. The conclusion is that our projects, our challenges could be very different - we need very different formulas to use.

VALUE was also questioned - but we made a nice formula-reference fun with it:
{=VALUE(OR:NOT VA:LUE)}
=ISTEXT(FR:OM HAM:LET)
â˜º

The only thing you agreed was the un-necessity of BAHTTEXT - no one knows why this formula is there. â˜º

It was a great game, guys - at the end, we keep all the formulas, or even, want more!
That is why Paul Benson started the discussion: Desired Functions

First we mentioned the famous CONCATENATE - why it does not work with ranges? We have great VBA solutions for this problem. Some of you need some other string-related formulas, mainly more sophisticated search/find possibilities. Paul mentioned bitwise operators - and Roberto again shown us how can we build up these formulas using the existing base-formulas in array-formula. Many of the commenter's mentioned that user defined formulas could have been built up for the special requests.

Jay Hunnemeyer stated a great conclusion: "The beauty of Excel is that, with its inherent set of functions, and VBA, most anything imagined can be accomplished!"

However I could imagine what Gordon Cooper told: "=thisweekslottonumbers() would be useful :)"  -  maybe it is the only one could not be done with VBA? â˜º

Playing with formulas is always interesting - these kind of discussions are sure successes. Some examples: In one of Roberto's challenge we created formulas to count how many different characters are in a text expression. It was a great challenge with 3 different formulas: for case sensitive, non-case sensitive and for all (including non-ASCII) characters. It was nice to see how we used some parts of each other's formula to build up a shorter formula.

Here is a solution combining the suggestions of Daniel and Kris for counting all the characters in a case-sensitive way:

=SUM(N(FIND(MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1),A1)= ROW(OFFSET(A1,,,LEN(A1)))))

The conclusion: "when you take away all the superfluous ... formulas seem so easy ..." by Roberto.

Another favourite of mine is: How to create an array of numbers from a comma-separated text entered into A1 like: 8,9,11,13 (Array question for the experts! By Alun Hewinson)
• Solutions from No One H. and Roberto Mensa:
• =SUM(1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),ROW(INDIRECT("1:"&(1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))*999-998,999)))
• =SUM(--MID(SUBSTITUTE(A1,",",REPT(" ",99)),1+99*(ROW(OFFSET(A1,,,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1),99))

Seems the speciality of our group is to provide adequate solutions to the group members questions. Really good to read these kind of sentences:

"Thank you all so very much for taking the time to offer your solutions, I really do appreciate it. This has really shown me the power of LinkedIn and selecting the right Groups to join!" Mark Lowe, FCCA, MInstLM.

To reach a wider audience, Daniel decided to open the group. That means all discussions started in the open group are fully visible, searchable, and shareable on the Web. We have two managers: Suzanne and Kris - their mission is to keep this place energized: stir the pot to encourage group member participation and group membership growth. The first month is amazing! We have 58 new discussions and 630+ comments within a MONTH!

We started a poll to get to know you better. After 135 votes we can see that significant part of our readers are Excel Power user or Excel developer. And take a look at the number of Professional Excel/VBA Developers! It is the proof that we attract the best minds! â˜º

Many of you (especially who are Excel Power Users) mentioned that you are learning or plan to learn VBA, so I would like to highlight a post by Jordan Goldmeier: Top 5 VBA Development Environment Tips. We collected very useful and important tips there. This discussion could be a "must read" for all who starts to learn VBA.

What is in your PERSONAL.xlsb? is a popular discussion, we shared several great VBA codes, and sometimes suggested changes/corrections/better solutions.

Excel VBA - avoid or use? Here we also have some great tips and thoughts. Many of the commenters mentioned that the Macro recording tool could be a good starting point to get to know VBA.

And... we continue to ask, to talk about formulas, to solve challenges in many different way, to teach, to share knowledge, to post great examples, teaching materials...

Let's continue this way!

---

Please checkout he Excel Hero LinkedIn Group. It is certainly one of the most intriguing Excel focused meeting places on the net.

## Motion Induced Blindness

Hello All.

My name is Ian Huitson "Hui".

I am quite honored that Daniel has invited me to become a regular contributor here at the Excelhero.com/Blog and hope I can maintain the high standard on content that this site has become renowned for. I look forward to your feedback on this and future posts.

A few months ago I spotted a new optical illusion over at http://www.michaelbach.de/ot/mot_mib/index.html

I figured I'd give it a go in Excel and this post documents my approach to a solution.

Motion Induced Blindness

What to see

On the right you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?
In reality, the 3 yellow spots are continuously present, honest! This is captively called "motion induced blindness" or MIB.

What to see (copied from michaelbach.de)

Below you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?

n reality, the 3 yellow spots are continuously present, honest! This is captively called "Motion Induced Blindness" or MIB.

What to see

On the right you see a rotating array of blue crosses and 3 yellow dots. Now fixate on the centre (watch the flashing red/green spot). Note that the yellow spots disappear once in a while: singly, in pairs or all three simultaneously, right?
In reality, the 3 yellow spots are continuously present, honest! This is captively called "motion induced blindness" or MIB.

The actual MIB Excel model is much smoother than this animated GIF representation.

The MIB Model

There were three approaches I thought about using for this optical illusion.

1.   Use a single series to define all the points (49) and place a cross at each point.

2.   Use 2 series to define each cross, there are 49 crosses.

3.    Use a Bitmap for the Background including crosses and rotate it.

Choice of Attack

The first method wouldn't suit the needs of the illusion as each marker doesn't rotate as the series is rotated, but instead stays fixed relative to the ordinal axis.

The second method would require a large number of co-ordinates for each cross, that is 4 X and 4 Y co-ordinates for each cross and there are 49 Crosses, for a total of 98 series, and 196 co-ordinates. Time consuming but at least the crosses will rotate. This is the methodology I choose for the model.

The Third method of rotating a fixed bitmap although very feasible, wasn't I felt in the spirit of doing it all in an Excel Chart. I should note that this method would allow for much faster rotation than has been achieved using Method 2.

You can follow along with the real model and all associated preparatory workings in the attached file: Motion Induced Blindness.xlsm. All preparatory workings described below are on worksheet "2".

Setup

Firstly I setup a table of numbers -3 to +3 in X and Y and then added/subtracted a small amount to each one to represent the width of the cross. I settled on 0.15 as it looks about right.

This gave me a table of X and Y values for each point.

 Offset 0.15 Pt No X1 X2 Y Circle Quadrant 1 -3.15 -2.85 -3 3 2 -2.15 -1.85 -3 3 3 -1.15 -0.85 -3 3 4 -0.15 0.15 -3 3 5 0.85 1.15 -3 4 6 1.85 2.15 -3 4 7 2.85 3.15 -3 4 8 -3.15 -2.85 -2 3 9 -2.15 -1.85 -2 3 10 -1.15 -0.85 -2 3 etc

Rotation

The problem with X and Y values is that to rotate them around a point it is easier to use Polar coordinates, but Excel requires Cartesian Coordinates to plot.

So the process would be

1.       Setup the 49 points of 4 sets of X, Y pairs

2.       Transform them to polar co-ordinates.

3.       Setup a named formula for each end point

5.       Use a named formula to convert the polar coordinates to Cartesian coordinates

6.       Plot

7.       Repeat from Pt 4.

Polar Co-ordinates

Point 2 in the above sequence means converting each set of coordinates into polar co-ordinates consisting of Radius r and Angle Ã˜.

Solving the above we have

r           =sqrt(x2 + y2)

= Sqrt(-2.852 + -22)

Ã˜          = Atan(y/x)

=Atan(-2/-2.85)

This is done for every end point of each cross section, 98 pairs of X, Y Co-ordinates.

Now we have the polar co-ordinates of the end points, we can setup the rotation equations.

That is the equations to convert the original polar co-ordinates back to X & Y values, which Excel needs to plot.

This is done by 2 simple equations:

X = r * Cos(Ã˜)

Y = r * Sin(Ã˜)

X = 3.731*cos(3.707)

Y = 3.731*sin(3.707)

Now we can add a rotation angle, lets use t.

So that the new position after rotation is

X = r * Cos(Ã˜ + t )

Y = r * Sin(Ã˜ + t )

Xrot = 3.731*cos(3.707 + t)

Yrot = 3.731*sin(3.707 + t)

This is done for each point of the cross for all crosses.

 x1 y1 4.350*cos(3.903+t) 4.350*sin(3.903+t) 3.691*cos(4.091+t) 3.691*sin(4.091+t) 3.213*cos(4.346+t) 3.213*sin(4.346+t) 3.004*cos(4.662+t) 3.004*sin(4.662+t) 3.118*cos(4.988+t) 3.118*sin(4.988+t) 3.525*cos(5.265+t) 3.525*sin(5.265+t) 4.138*cos(5.472+t) 4.138*sin(5.472+t) 3.731*cos(3.707+t) 3.731*sin(3.707+t) 2.936*cos(3.891+t) 2.936*sin(3.891+t) 2.307*cos(4.191+t) 2.307*sin(4.191+t) etc.

Matrix Arithmetic

To draw a line on a scatter chart, Excel needs 2 X values either in a Range or an Array as well as 2 Y values in a Range or Array.

Thankfully I've been a member of Daniel's Excel Hero Academy. In a Module on Matrix Arithmetic we learn that we can add 2 named formulas together to make an array in a Named Formula.

We need to do this to end up with an Array representing the X and Y values for each of the 98 segments of the 49 Crosses.

X Values = { X1, X2 }

Y Values = { Y1, Y2 }

As an Excel Named Formula I used:

Named Formula   Formula

sx_08                     = {1,0} * 3.731*cos(3.707+t)  + {0,1} * 3.482*cos(3.753+t)

sy_08                     = {1,0} * 3.731*sin(3.707+t)  + {0,1} * 3.482*sin(3.753+t)

This is done for all the 98 cross segments.

To simplify the construction of all these, the co-ordinates, transformation to polar coordinates and construction of the rotated transform formulas was done in Excel (Refer Worksheet "2" in the example file).

This allows errors in co-ordinates to be checked.

Once all the named formula are ready to be uploaded, I have used a technique involving a simple VBA Named Formula upload subroutine. This is described in my post at: http://chandoo.org/wp/2011/06/23/automating-repetitive-tasks.

The VBA routine is available in Module 2 of the attached Sample File, "Load_Named_Ranges()".

Once the named formula are constructed and loaded, it is simply a matter of adding a blank scatter chart to Excel and setting up a table of Series Names, X value and Y Values:

 Chart Series Name X values Y values S01 =1!sx_01 =1!sy_01 S02 =1!sx_02 =1!sy_02 S03 =1!sx_03 =1!sy_03 S04 =1!sx_04 =1!sy_04 S05 =1!sx_05 =1!sy_05 S06 =1!sx_06 =1!sy_06 S07 =1!sx_07 =1!sy_07 S08 =1!sx_08 =1!sy_08 S09 =1!sx_09 =1!sy_09 S10 =1!sx_10 =1!sy_10 Etc

Once again I have setup a table of Named Formula name, together with X and Y Named Formula and used a small VBA routine to add these series to the chart.

The VBA routine to do this is available in Module 2 of the attached Sample File, as "Add_Cht_Series()".

The 3 Yellow Spots

The 3 yellow spots are a manually loaded series in the chart using an Array of coordinates.

X Series                              ={1.5, 0, -1.5}

Y Series                              ={1.5, -1.8, 1.5}

The Marker was set to Yellow and size 15

The Line Type was set to None

The Centre Spot

The centre spot was a manually loaded series in the chart

X Series                              =0

Y Series                              =0

The Marker was set to Red and size 12.

The Line Type was set to None.

Animation

Animation of the chart is achieved by adding a simple Named Formula "t" and the changing the value of t and updating the chart.

This is done through a simple VBA routine "Rotate()"

This is described below

Sub Rotate()

Dim t As Double 'Dimension the only variable

t = 361 'Start at 361 Degrees

Do While [AA1]   'Loop while cell AA1 is True

t = t - 1 'Decrease rotation angle by 1 Deg

If t = 0 Then t = 360 'If Rotation = 0 go back to 360

ActiveWorkbook.Names.Add Name:="t", RefersToR1C1:=(t * 2 * Pi / 360)

' Add a named Formula t with value = t * 2 * Pi / 360

DoEvents 'Refresh screen

If (t >= 0 And t < 90) Or (t >= 180 And t < 270) Then 'If t in a range set Centre Marker color Red or Green

ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(99).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

Else

ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(99).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)

End If

Loop

End Sub

The above example is attached below:

Worksheet 1, contains the working model.

Worksheet 2, contains the original source data as well as all transformations of it.

FINALLY

This is my second post at ExcelHero.com and I'd like to thank Daniel for allowing me to post here again.

I am a member of the inaugural Excel Hero Academy and MVP of the Excel Hero Academy 2 & 3, where Daniel explains a lot of the techniques you will see throughout this site as well as so much more.

It is one of these techniques that made this project possible.

I am a regular contributor at Chandoo.org where I answer questions at the Forums and have contributed over 30 Posts.

## Time to Learn!

| 1 Comment | 0 TrackBacks

It is with great pride that I announce that the third class of the Excel Hero Academy is now open for registration!

We are definitely growing.  I've hired my first employee!

If you have been waiting for an opportunity to truly understand how Excel works, the time is now.

But hurry because registration will only stay open for two weeks and the next class will not be until summertime.

But whether you decide to enroll or not, please let me know what you think of our brand new Enrollment Page!

.

• Atom feed
• Daniel Ferry