Over at the Excel Hero LinkedIn Group, I just posted a very tough Formula Challenge for converting numbers to written words.

The objective is simple: beat my formula!

Can you do it?

Over at the Excel Hero LinkedIn Group, I just posted a very tough Formula Challenge for converting numbers to written words.

The objective is simple: beat my formula!

Can you do it?

Right now the Excel Hero Academy is full to the brim with students learning how Excel really thinks.

A big component of this is VBA or Visual Basic for Applications. While VBA is the mechanism that Excel uses to record macros, it is so much more than this. It’s a full fledged proramming lanuguage in it’s own right and when you understand how it works, you begin to understand the rest of Excel in a completely new light.

If you want to weild Excel to do your bidding in your workplace, VBA is a must.

Chandoo’s VBA Class dovetails perfectly with the Excel Hero Academy course. You will be perfectly prepped for the intriguing concepts I present in the Academy. I highly recommend it.

Chandoo has just opened registration for his new VBA Class – which is a 12-week, comprehensive VBA course. If you know your way around regular Excel and have always wanted to learn how to devise your own VBA and not just copy a snippet you found somewhere, than this is the course for you!

At the bare minimum, please click on this image and watch a video of Chandoo (who is a Microsoft Excel MVP) describe the course in detail:

Chandoo’s courses always offer extraordinary value for money and this course is no different. But you can also choose to bundle it with his flagship Excel School and Dashboard programs. Extreme value indeed!

There is no doubt that enrolling in Chandoo’s VBA Classes will prepare you well for my upcoming VBA course or the regular Excel Hero Academy course, so please keep that in mind.

If you are reading this in email or RSS and connot see the above form, please click here.

As of right now, over 250 students have enrolled in Excel Hero Academy 2.

But registration is closing very soon, August 15.

If you have been wanting to do this course, now is the time to enroll.

I’ve been answering a lot of questions about the course lately, so I thought it would be good to post some of that information here.

You should expect to put in between 2 and 4 hours each __week__ to study the course materials and do your homework.

What’s the schedule? Do I need specific hours each day?

The 12 modules are released on a schedule. It happens each Tuesday. Right now modules 1 and 2 are already posted in the academy. Module 3 comes next Tuesday. After every three modules, we have a “review week” that is dedicated to reinforcing the concepts from the previous three modules. We learned in the first class that this is absolutely needed, as there is an incredible amount of information and much of it is unfamiliar to most students.

Once a module is posted, it stays available for the duration of the course. On the regular admission level, this means six months from August 15. On the Extended Enrollment, this means one year.

All of the study is done on your own, on your own schedule, and at your own pace. Having a set time to meet with the students is virtually impossible since students come from many different time zones around the world. I spend a lot of time interacting with students on the campus forums. And the students do an incredible amount of inteteracting amongst themselves.

Additionally we have four Excel Hero Academy MVPs that assist in moderating the forums and answering questions. Each of the MVPs are alumni of this course and are extraordinarily helpful and knowledgeable. Their collective Excel knowledge is staggering.

You may know these EHA MVPs already from their huge contributions around the blogosphere. They are Hui, Robert Mundigl, Jeff Weir, and sam.

I am happy to announce that I have just now expanded the video options to include the iPad! You do not need any sort of plugin for the iPad, as the videos are in Apple’s native QuickTime format. This is really a great new feature if you have an iPad. The video streams full screen and it is crystal clear!

For normal computers, you need Flash installed, but virtually every computer has this.

This is a great idea. I have chosen one video from the dozens in the course. This video is a very basic VBA Primer, but it gives you some great information, but more importantly allows you to see the quality of the video. All of the videos in the course are very much HD, at a resolution of 1024 x 768. They are razor sharp and incorporate no zooming or panning. The zoom level is set at 100% for the entire presentation.

Notice the easy way that I introduce subjects. Many students have complimented my teaching style. Please judge for yourself.

If this video works for you, then all of the videos in the course will work. They are all the same size with the same encoding.

Here are the links to the EHA sample video:

Yes. If you choose the Extended Enrollment Option, you c

an download the videos for personal viewing, after the 30-day Guarantee period passes. The Extended Enrollment also includes three additional weekly modules.

an download the videos for personal viewing, after the 30-day Guarantee period passes. The Extended Enrollment also includes three additional weekly modules.

Registration for the only class of the Excel Hero Academy in 2011 closes at midnight on August 15.

Please enroll now!

I just received this bit of interesting news.

Thanks, Corality. I’m honored.

By the way, there’s still six days left to enroll in this year’s Excel Hero Academy. Don’t miss out!

It’s been 19 months since I started the Excel Hero blog.

Excel Hero has grown into quite a number of operations and groups since then. I feel it is time to get a professional brand going.

So I’m holding a logo design contest at 99designs.

So far there have been over a hundred entries, most of which I did not care for. But some hold some promise even though “The One” is not there yet. But the contest is an interactive process, so I will be guiding the designers.

Please help me by providing your comments here. You can refer to the entries by number.

And hey, if you have the talent, enter a design. The contest pays $695.00!

I have been very descriptive in the Contest Brief.

Leave your feedback here or at the Excel Hero LinkedIn Group.

I am very happy to announce that the Excel Hero Academy will be starting the 2nd group of students on August 1, 2011.

I know many of you have been waiting the better part of a year for this. The wait is almost over!

I am pleased to announce that the firs 100 students to enroll will be eligible for the Early Bird Special discount of $50. The actual buy buttons won’t be in place on the enrollment page until first thing, August 1, Pacific Standard Time.

To claim the Early Bird Discount when enrolling, please enter the code: HEROBIRD.

If you are not on the EHA Interest List, what are you waiting for?

Note: If you cannot see this clock, click here.

Sometime ago, I stumbled onto an interesting narrated chart at the NY Times that told the story of Oil’s price and consumption.

I thought it would be an interesting challenge to recreate this chart in Excel. Here’s the result…

Click on the above image to see and hear a Flash video of this Excel 2010 chart in action!

I think this narrated and animated chart concept is very compelling for business presentations. I’m sure I would not have understood the story the chart tells, just by looking at a static version.

I love the fading images and how effectively they stir memories. They really add to the chart story.

There’s a lot of interesting bits happening behind the scenes with this one. Jad’s narration is a recorded WAV file from the NY Times original. I’m using DirectShow from VBA to play and pause the WAV file. It’s important for the WAV file and the Excel workbook to be in the same folder, so please unzip them together before opening the workbook.

The animation plays at a different speed on every computer, so the big challenge with this project was to devise a way to keep the WAV file, which plays at a constant rate in sync with the animation, on every computer. I’d be interested in your feedback on how well the solution I settled on (synchronization points) works.

Tear this thing apart and figure out how it works. There are a lot of valuable techniques showcased with this chart project.

Here is the zip archive:

If learning advanced Excel interests you, please stay tuned. I will be making a major announcement on Monday regarding the next session of the Excel Hero Academy!

Add yourself to the Excel Hero Academy interest list. Just fill in the form on this page.

Here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

<

div>

On July 1, 2011, I woke to a very pleasant surprise.

In my inbox was a message from Microsoft congratulating me on the fact that they had just awarded me an Excel MVP for 2011!

I am not exactly sure what this means yet, but it is exciting.

I just wanted to say thank you to Microsoft and to the anonymous nominators!

The physical award is being shipped currently. When it arrives I’ll publish a photo here…

** **

**A
Guest posty by Ian Huitson (Hui**

Firstly, a thank you to Daniel for allowing

me to make this post on his Excelhero.com blog.

I have admired Daniels work since he first

started this blog and this post will demonstrate some of the Charting, Named

Formula and VBA techniques that I have picked up from studying the examples highlighted

throughout the site and from what he also teaches in the Excel Hero Academy.

**DANCING
PENDULUMS**

A few weeks ago I visited the Newton Excel

Bach web site where I spotted the Dynamically Defined Dancing Pendulums.

Having noticed that Doug had done a nice

animation in Strand7 (a Finite Element Analysis program), I thought “I can do

that in Excel” and so I did.

This post looks at the techniques used

within Excel to produce this model.

I am not going to describe the Physics

involved in Pendulum motion here but I am going to discuss the implementation

of this model in Excel.

The Pendulum Project is broken into 4 Main areas:

·

Pendulums

·

Control

·

Animation

Each is discussed below.

** **

** **

**PENDULUMS**

A pendulum consists of a Fixed End

(Fulcrum), Moving End (Bob) and Connector or Wire.

I am not going to describe the Physics

involved in Pendulum motion nor the mathematics

But we need to know a few simple things about

the pendulum.

One point will be fixed at the fulcrum and

assigned an arbitrary location of (x=0, y=0), we choose this to simplify the

maths, the other/moving end, the Bob, will move according to the equations of

pendulum motion.

For a single Pendulum we need to know the

location of the pendulum at any time, t.

The location is expressed in angular terms

as ø = ø_{max}.sin( sqrt(g/L ).t ), where ø is in

Radians and · is multiply, G is Gravitational Acceleration 981.24cm/s^{2}

and L is the Pendulums length.

Knowing the pendulums angular location ø

and its length, we can transform that to X & Y coordinates using simple

geometry.

** **

** **

**In
Excel**

In Excel a Pendulum can easily be

represented on an Excel Scatter Chart as a single series consisting of two points

with a joining line.

We can then add any number of new series to

represent different pendulums.

Each Pendulum is modelled as a single

series consisting of 2 coordinates, one being the fixed or Fulcrum end which

we will locate at (0,0) and the Bob of the Pendulum, which will move according

to the equations for the pendulums motion.

To setup the pendulum I have used Named Formulas for the Coordinates of the Pendulum.

The advantage of Named Formulas is several

but Named Formulas,

·

Calculate blindingly fast;

·

Are flexible enough to allow us

to represent our two point series; and

·

Can easily be controlled using

some simple VBA code to allow animation

·

Can easily be controlled using built

in Excel Controls.

** **

**NAMED
FORMULAS**

To setup a Pendulum in Excel we will use a

number of named formulas.

** **

**General
Named Formulas**

t =0 Run

time starts at 0 and increments every unit

g =981.24^{ }Gravitational

acceleration, cm/s^{2}

OMax =’1’!$B$4*PI()/180 Max angle of

Pendulum, radians.

** **

**Named
Formula for each Pendulum**

Each pendulum is represented by 4 Named

Formulas, starting with p1 for pendulum 1 and then a suffix for the various

measurement:

p1Len | =’1’!$B$9 | The Length is stored on the worksheet (Discussed later) |

p1o | =OMax*SIN(SQRT(g/p1Len)*t) | Current angular position of Pendulum 1 at time t |

p1x | =p1Len*SIN(p1o)*{0;1} | Current orthogonal X position of Pendulum 1 at time t |

p1y | =-p1Len*COS(p1o)*{0;1} | Current orthogonal Y position of Pendulum 1 at time t |

The formula for the X and Y locations are

worth examining. The Excel Scatter Chart needs a Range of

size 2 to retrieve the Start and End X locations of the Series and an

associated range for the Y values for that series.

In this model we will be using a Named Formula for the X and Y values. This is done in the Named Formula using an Array.

So for the X Series =p1Len*SIN(p1o)*{0;1}

The {0;1} are the two points of the Series

representing the pendulum.

In this case values of 0 and 1 have been

used.

The Fulcrum End will always return 0 as it

is multiplied by 0

The Bob end of the Pendulum will return the

value p1Len*SIN(p1o)

And similarly for the Y values for the

series =-p1Len*COS(p1o)*{0;1}

Noting once again the use of the Array {0;1}

for the series and the** ‘-‘** so that

all Y values are below 0.

It should be noted that these 4 formulas could have been combined into 2 formula for the X & Y position as:

p1x =’1’!$B$9*SIN(OMax*SIN(SQRT(g/’1’!$B$9)*t))*{0;1}

p1y =-‘1’!$B$9*COS(OMax*SIN(SQRT(g/’1’!$B$9)*t))*{0;1}

This would have meant that there would only

be 32 equations for the 16 Pendulum rather than the 64 used.

It is felt that for clarity of discussion

and readability of the example this method would be clearer.

Side project

The above 4 named fromulas need to be entered

for each of the other 15 pendulums.

During this project I instigated two small

side projects.

The first was a small piece of VBA code to

import the named formulas into Excel from a range of worksheet formulas.

The second was a small VBA code to add the

16 series to the Chart with all the details once again sourced from a worksheet

Formulas.

The construction of these two projects is

covered in detail at Chandoo.org.

** **

**THE
CHART**

Once the named formulas are established, a

Chart can be added to the sheet.

A new series is added to the chart for each

pendulum

Series Name: 1

Series X Values: =’1’!p1x

Series Y Values: =’1’!p1y

These were repeated for each of the 16

Series. Once again this process was automated and is described in detail at Chandoo.org.

Each series was then selected and a Built

in Circle Marker put at one end of Size 15 and a small marker put at the other

end.

The Axis need to be scaled so that the

scale of each axis is equal or the pendulums will swing in a non circular path.

This is easily done manually by adding a Square Shape to the Worksheet and changing the chart’s size so that the two axis are the same length.

Once they are correctly scaled the Axes can

be removed along with the Titles and Grid Lines, by selecting them and pressing

**Delete**.

The Bob was then changed to a Circle, The

fill color was set and it was resized to 15 Pts. The Pendulum’s wire, the

series line, was also set to a Black Color and resized to 0.5 Pt.

The above is then repeated for each

Pendulum.

In the Controls section below, I will discuss

how to change the colors of the Bob and Wires of the pendulum during the

animation.

** **

**ANIMATION**

In the above section we saw how to setup a

Pendulum as an Excel scatter chart based on a number of Named Formulas. The Named

Formulas ultimately rely on a single named constant, t, which holds the time value.

To animate the chart all we need to do is

increment the time named formula and the chart will adjust itself to the new

position based on the revised time.

To do this I have used a simple piece of

code which is shown below:

The animation code:

Sub Pendulum() Dim ti As Double Dim i As Double ' Stop people breaking execution Application.EnableCancelKey = xlDisabled ' Set a default Mouse Cursor while animating Application.Cursor = xlNorthwestArrow ti = [tinc] Do While [D7] = "True" For i = 0 To 2000 Step ti ' Add a Name to the Name Manager ' called t with a new Time Value i Application.Names.Add "t", i DoEvents ' Exit if Start/Stop Pressed If [D7] = "False" Then End Next Loop ' Reset Mouse Cursor Application.Cursor = xlDefault End Sub

The code loops a variable i from 0 to 2000

in steps obtained from B3 via the ti variable.

For every loop of the variable i, the code:

·

Stores a new named constant ‘t’

with value = i

·

Executes a DoEvents command

which:

o

allows the Chart to Update

based on the new X & Y values as a result of the new time ‘t’

o

Checks any keyboard/mouse input

·

It then checks the value from

D7 and if False exits, or if True continues

The use of minimal and simple VBA code and named

formulas results in a system that can update quick enough to result in a very

smooth animation.

** **

** **

**CONTROLS**

A number of user definable controls have

been added to the Pendulum Project

The most obvious of these are the Check

Boxes and Option Buttons on the Chart, but there is also a number of numerical

inputs and a slider.** **

**Time
Inc, Swing (Max) and Gravity **

The 3 cells B3:B5 hold values for the Time

Inc, Swing (Max) and Gravity respectively.

These can be changed either before execution

of the pendulum or during execution. The Gravity is also controlled by the

slider to its right. The slider is linked to the cell D5 under the slider and

the Cell B5 divides the sliders value by 10.

The other two cells which allow manual

input are C9 & C10 and these will be discussed in the Pendulum lengths

section.

The Main Controls are Form Control

Controls.

The Start/Stop Check Box is linked to the cell

D7 as well as executing the **Sheet1!Pendulum_Animate**

subroutine which controls the animation.

The Pendulum Color Option Buttons are

grouped and linked to E9 and pressing any option button executes the **Set_Pendulum_Colors** subroutine. Which

reads the value of Cell E9 and calls the **Color_Markers**

subroutine with the Color type (1=Colored, 2=Grey & 3=Zebra) as an option.

The Wire Color Option Buttons are grouped

and linked to E10 and pressing any option button executes the **SetWires** subroutine. Which reads the

value of Cell E10 and calls the **Color_Wires**

subroutine with the with the Wire type (1=Black, 2=Grey & 3=None) as

options.

** **

**Reset
Parameters**

A subroutine, **Reset**, has

been provided which allows either user defined reset of all parameters as well

as on being executed automatically on startup.

Sub Reset() With Worksheets("1") .Range("D5").Value = 981.42 'Gravity .Range("D7").Value = "False" 'Starting position .Range("E9").Value = 1 'Bob Color .Range("E10").Value = 1 'Wire Color .Range("B3").Value = 0.015 'Initial Time Inc .Range("B4").Value = 15 'Initial Swing Angle SetColors SetWires End Sub

**PENDULUM
LENGTHS**

Earlier in the post I mentioned that the

use of named formulas was much faster than cell references, which it is. So why

are the pendulum lengths stored on the worksheet?

The calculation of the length of the

pendulums is important to ensure the harmonics are correctly modelled.

To get the nice synchronous effects seen

during a long run of the animation the series must be in harmony with each

other. This simply means that after a certain time that the pendulums will

re-align themselves.

The simplest way to do this is to choose

lengths of pendulums which are suited to integer repetitions within the same

time frame.

That is, choose a pendulum length so that

its natural frequency is evenly divided into say 60 Seconds.

The cell formulae from B8 to C24 do just

that.

Column C sets up a number of Integer

Frequencies based on the values in C8 and C9.

The lengths in Column B are then pro-rated

to a standard 60cm pendulum based on the required Cycles/T.

You can change C9 & C10 during

execution to see what effect it has.

** **

**LINKS**

**CLOSING**

You can see by the animated GIF at the top

of the post and the attached workbook that smooth animation of 16 Pendulum is

possible within Excel.

** **

Here is the workbook:

Huis_Excel_Hero_Pendulum.xlsm.

** **

For best results, run the dancing pendulums with no other workbooks open…

** **

**Hui…**

Here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

When we say Excel XML, there are several things we could be referring to. In fact, the newer Excel file formats actually use XML natively to save workbooks – these are the Excel file types that end in “x”, such as my_model.xlsx.

But what I am writing about today is how easy it is to use XML in Excel. XML files are often from a web source that keeps the information within the files current. Sometimes these sources are referred to as web services, as the files can be generated from a database, on demand.

If you want to use XML data in your workbook, you can do so in a huge number of ways with VBA. But there is an extremely simple solution that requires no programming whatsoever. The easiest way to facilitate an XML to Excel conversion is to employ the New Web Query dialog. Just select the Data tab on the Ribbon and then click on From Web. Enter the address of the XML file and click Go.

Excel does a great job of removing all of the XML tags, leaving just the data we want. Click Import. If the XML file does not refer to a schema, Excel creates one automatically and is very accurate when doing so.

The result is an Excel Table placed at the location you specify. You can now use this data in any way you would normally use Excel data. But there is a huge bonus here. You can REFRESH the table whenever you like and if there is newer data available from the source, the table updates itself. This means your entire model can be updated, simply via an update at the source.

To demonstrate, please download my dynamic Periodic Table Excel template.

It is built on the foundation of several XML source files. To be sure, the data that describe the chemical elements do not change at break neck speed, but they do change as new elements are discovered and better experimentation procedures produce more accurate property values.

I modeled this template based on a dynamic web periodic table that I created in 2007. It works great in Chrome, Firefox, and Safari, but only works (slowly) for version 8 of Internet Explorer. If you scroll to the bottom of the web version, you will see the links to the XML data.

The Tables in the Excel version use these XML links. So when I update those XML files on my server, your copy of the Excel template will update as well. Cool, no?

This Excel XML technique could be used in a myriad of business applications.

Please let me know what you think of this technique, the Excel template, and even the dynamic web Periodic Table.

Advanced interface design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning.

I’ve received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they’ve ever made. Wow.

In a few weeks I will be offering the course again. There’s already over 300 people on the interest list for the next class. Why not join us? Just fill the form below and I’ll send you more information as we get closer!

And finally, if you have some interest in my upcoming VBA-only courses offered through the academy, please fill in this interest list form:

If you are reading this in email or RSS and connot see the above form, please click here.