Master Excel PowerPivot Now

| 0 Comments | 0 TrackBacks


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





.

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.


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





Regional Excel Differences - Leave Your Mark

| 0 Comments | 0 TrackBacks
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!

Advanced Excel Training!

| 2 Comments | 0 TrackBacks


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

| 0 Comments | 0 TrackBacks


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.


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





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!





Enchanted Radar Chart

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.

Filled radar data series:
  • 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.


How do we manipulate our data to build up the series?

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 formulaNoteRefers to
n_ColPointsPredefined number of data points for one category + 1 point for the gap=9
n_CategoryNumber of categories in the data table. In the example n_Category = 20=COUNTA( Sheet1!$A$1:$A$100 )
n_TotPointsTotal number of data points on the radar chart
In 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_hn_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:

(please note, your row and column separator character could be different, depending on your regional settings.)
arr_pntrow-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_catcolumn-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_catthis 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 )+1

or 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_gapsequence 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_01Data 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_1ISNUMBER 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:
labelsDynamic range for labels=OFFSET(rng_h,,-1)


And insert it to the series formula of the ring via the formula bar:
=SERIES( "label_1",
Enchanted_radar_chart.xlsm!labels,
Enchanted_radar_chart.xlsm!label_1, 4)

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_catThis 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_a1Comparing 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_a2Choose 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_rngWe 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.

TTThis 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.


Download

You can download the example file described in the article:

Enchanted_radar_chart.xlsm



And you can find some more charts with this technique here:
https://sites.google.com/site/e90e50fx/home/infographic-chart-with-excel

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



To learn MUCH more about Excel formulas, please go here:

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

| 0 Comments | 0 TrackBacks


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
  • Document your work
  • Share your knowledge with your colleagues
  • 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! ☺

 
excel_hero_poll.png

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

| 6 Comments | 0 TrackBacks


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.


MIB.gif

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.

Illusion Pics1a.png


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

Illusion Pics2a.png


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.

Illusion Pics4a.png

 

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

4.       Add a radial increment to the revised polar coordinates

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 Ø.

Illusion Pics3.png

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()".

 


Add Chart Series


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

      ' t expressed in radians

      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



Download

 

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.


Download here: Motion Induced Blindness.xlsm

 

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.


For more about my Excel work please visit: http://chandoo.org/wp/about-hui/



 

Enhanced by Zemanta


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!



photo4.jpg


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!





.

Excel VBA Class 2012

| 8 Comments | 0 TrackBacks

The next class of the Excel Hero Academy will open on February 1, 2012!

There's already a couple of hundred students on the waiting list, and many of them have been waiting since last summer.

While EHA is not a VBA class per se, Visual Basic for Applications is a significant component of the class because of the sheer power it brings to the table.  VBA is the mechanism that Excel uses to record macros, but it is so much more than this.  It's a fully fledged programming 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.

Think about this.  Even if you cannot distribute workbooks in your organization that contain macros, you can still use VBA to craft incredibly awesome workbooks that contain no VBA themselves.  The resulting workbooks may then be shared with your colleagues.

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

The way that I teach VBA is very intuitive, but I have you jump straight in to the deep end.  For some, it can be helpful to have some foundation first.

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 21-hour, 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 also a Microsoft Excel MVP) describe the course in detail:

vba-classes-msg1-chandoo.png

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 the 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.