Coloring the scatter plot in Excel in groups

Scatter plots are generally a nice tool to represent data, especially ones where we’d like to show a pattern of some kind. But what happens when we have a sub-pattern of data which we need to plot to understand the pattern in the scatterplot?

Let’s take an example here. This is how the data looks -

Name	X – Axis	Y – Axis
Region1	3%	17%
Region1	4%	34%
Region1	3%	14%
Region1	3%	37%
Region1	4%	28%
Region2	6%	23%
Region2	5%	30%
Region2	7%	33%
Region2	6%	25%
Region2	6%	30%
Region3	7%	50%
Region3	7%	70%
Region3	6%	59%
Region3	7%	48%
Region3	8%	59%

 

and the scatterplot like this…

 

 

 

 

 

 

 

But we’ve got more information that could be plotted, only it’s not directly possible in Excel. The regions are a second level of data that could aid us in plotting and visualizing the data, maybe there some kind of pattern within the grouping that we’re not able to see currently from this chart!

Take a look at how this might look -

And what a difference it seems to make on what we can infer from the chart!

Wouldn’t it be great if we could do this instantly? Well, excel doesn’t seem to be supporting such a feature yet directly from the menus, but I guess we’ve always got the folks who just can’t keep it quiet when it comes to tweaking!!

Here’s a nice link that shows how this can be done!

http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx

As for me, I’ve got my own piece of code, not the most robust & efficient one, but definitely works! I’ll probably put that up sometime soon…

Enjoy the excel experience…

 

THANK YOU to all my READERS! HAVE A GREAT 2013~!

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 7,600 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 13 years to get that many views.

Click here to see the complete report.

 

Extracting Text from Right of String in Excel

How often have you wanted to badly extract text from the right of a text, just the way the LEFT function works from the left. I’ve just craved for a function like

RIGHT(A2,FINDRIGHT(“,”,A2,-1))

and it picks the characters from the right counting till it finds the ‘,’!

For instance, “Columbus, OH” is the text in a cell and you need to extract “OH” from the right after the ‘,’ – one way is to find where the comma exists and then find the number of characters from the right by subtracting the total length of string from it… too tedious! But that works…!
Here’s how it looks
= RIGHT(“Columbus, OH”, LEN(“Columbus, OH”) – FIND(“,”,”Columbus, OH”,1))

Now is that all? Have we a universal solution at hand? Or should I say, have I no more problems at hand? Well, as usual, the answer is ‘not really’! What if you had multiple commas in a text string – like “Columbus, OH, United States, 40023″ and you wanted the last part after the ‘,’ i.e. 400023. If we went about working the solution like above, we’ll have Three FINDs to find the location of the last ‘,’!

Worse still?? What if you didn’t know how many commas’s existed in each cell, i.e. if the number of commas in every cell is not fixed! Now that’s a problem and we sure would love to have a function like FINDRIGHT I mentioned above!

What’s interesting is, there indeed is a nice unique solution to this problem, quite innovative I’d say.. great logic!
Here’s the idea – how about if we counted the number of occurrences of the delimiter per cell, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!

Step1 :

Find number of occurrences (this is the most important step in all this)

= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,”")) (What a great way to get the number of occurrences – very thoughtful!)

Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)

Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))

Putting them all together -
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,”")))))

And there we have it…

Now this is also not without shortcomings, but we’ll take that up in another post. For now, this is a great tool to use, and just copy that last function and put it in the cell next to where you want to extract!

One great link that helped me with this is given below for reference -
http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba” target=”_blank

 

Getting filenames of all files in a DIRectory ~

A very elementary request, but this had us searching all over the internet for solutions! We wished to get all the files in a folder, as we had saved multiple data files of Excel in one folder, and went about retrieving data from each file as and when required.

Trying to accomplish this is VBA is one thing, but what if we didn’t need to update it every time, and just needed it this once.. I wouldn’t want to waste my head over this by creating some complex VBA stuff.. then, where do we turn to? DOS!

It’s been a while since I heard that being used actually! But felt quite good trying to work it out.. Anyway, here’s the code to getting the filenames into one text file from a folder.

1. Go to the file location in DOS using CD <PATH NAME>
2. DIR /B /O:N > filename.txt
3. DIR /B /O:N /S > filename.txt to get all files from subfolders as well
4. DIR /B > filename.txt also works
5. DIR > filename.txt gets all details other than file name too

Some basic stuff huh?!! But good to know it when you need it!

 

 

VBA Error - "code execution has been interrupted"

Reblogged from One Stop Analytics:

Yet another bug! I've been quite heavily into VBA lately, and that's one reason for such error posts! This has been quite a pain in the neck for VBA programmers, esp. if you haven't quite hit upon this before.

This is how the story goes...

You're heavily into the coding of your product, and suddenly, you figure out that you've entered an infinite loop.

Read more… 355 more words

Additions to this Post - Quite recently, I ran into this error again, and couldn't quite seem to fix it with the EnableCancelKey thing.. I had mistyped 'False' for 'xlDisabled'. But thanks to that, I found a new way (lot easier and quicker) to fix this thing - Just press Ctrl + Break twice, and the Break mode is disengaged! Isn't it something??

 

Must listen to all Music Lovers ~ My Own Recording

Mahaganapathim Fusion Cover
Artists-
Vocals – Sneha Gopal
Guitar Rhythm & Leads – Arun Ravi
Sound Engineer – Ankur Nehru (Tritone Labs)

Special thanks to Ankur for playing the Bass.

If the above doesn’t work, try this -
http://w.soundcloud.com/player/?url=http%3A%2F%2Fapi.soundcloud.com%2Ftracks%2F39839295&show_artwork=true

Or this direct link -
http://soundcloud.com/arun-ravi/mahaganapathim-fusion-cover

 

Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

A lot of the time, there are things that totally baffle you when you least expected them – limitations, extensions, workarounds, unavailability etc. And that’s exactly what I’d like to uncover in my ‘Did you know’ series! Without much talk in this one, I really want to keep these ones short and succinct, here’s the first…

DID YOU KNOW!

The Conditional Formatting of Excel 97-2003/2007 has the major limitation that only 3 formats can be specified (excluding the one that’s already present in the cell).

All this brings up the argument of why even give such a facility when it’s not really much use! This may not seem like a big limitation, but only when you really try to put this feature to good use will you be irked!

The Situation – I wanted a cell to be formatted in 4 different ways based on filter criteria.

1. A dollar format if it’s tabulating currency
2. A number format for frequency counting table values
3. A percentage when applicable
4. and finally when ‘#N/A’ appears, I’d like it to dissolve into the background!

And appears this is not what the conditional formatting can handle!

The workaround – VBA! Always!

Additional tip

There’s a ‘Stop if True’ feature in the Conditional Formatting rules. Conditional formatting executes the format rules in the order in which they are added! So, if you select this feature, the Conditional Formatting stops at that particular rule, and doesn’t go further! If I had to explain using code, it’s more like

If <condition = true> then

<body of statements>

<exit sub>

End if

If we ended up using an ‘If’ with an ‘Exit Sub’, that’s exactly what ‘Stop if True’ would do – exit the function if any of the ‘If’s’ is true! Although, given this limitation of just 3 formats, I’m not sure how much help this is – but surely, if you have just 3, this is a good one.