Bullseye chart – 2

Posted: September 2, 2015 in Charts
Tags: , ,

bullseye Chart

bullseye chart

Download the chart from here

LinkChart3 Download the file from here see Link Chart 2 see Link Chart 1 Thanks Rajan

I am playing with XY these days and come up with this Multi – Axis line graph, have a look here.

This is how this chart accept the data




Here is the chart screenshot



you can download the file form here

in case you want to learn you can unlock the file with “j33hqiwy8@12345”


Link Chart 2

Posted: February 16, 2015 in VBA

The last chart i posted here was showing item link to a category and vice versa , This one shows each items links with other items.



download the file from here
nlock the file with “j33hqiwy8@12345”

here is another graph i have created using XY to show the items under a category or category belongs to multiple items. see the below table that you can fill 1 in, and chart will update automatically.


Here you can select Category and Items in this table to the left and graphs will highlight the links












Download the file from here 


Tree Chart

Posted: February 11, 2015 in VBA

Hi Guys,

This chart shows the hierarchy, anyone can use this to build organization chart, family tree etc. i have tried to make it simple, user just need to fill the data in first two columns. and the graph will update automatically. feedback and suggestions are welcome.


Download the file from here


Excel provides SMALL and LARGE formula to get sorted numeric values , but sometime we need our string/text data to be sorted by formula only.

Though, there is an inbuilt sorting feature available in excel, but here I am going to demonstrate a formula.

So, Lets say you have your data list in Range “A1:A10”, Better to create a name instead of using direct reference into the formula. so I have create a name range that is “myList”



It might be looking a long formula to some of you,but believe me it is not,  because red part is same. Ok,lets evaluate this.

The base inside this formula is  this ,


this formula compares each value with each value in the list and create a two-dimensional array


 if you look at the 0 in B2 it mean that 10 in A2 is not greater than 10 in B1, and if you look at 1 in B3 , it means 12 in A2 is greater than B1, and so on. the next step is to sum this array by rows. So we will MMULT here with another array. See the  formula down below to get that second array, this should have the same number of row element as our first array but all the element would be 1

=ROW(MyList)*0+1   = {1;1;1;1;1;1;1;1;1}

and now we have two matrix to multiply , so here we use MMULT() function


After multiply that two matrix it will give a result like this table. 


Now we just need to sort these numbers, and we will use SMALL() function to do that. So here is the next formula


this will return these sorted numbers


Next , we need to match the correct index for all these elements from the above list.


this will return these index numbers


Now we got the index and we just need to get the Text from our original list


and here is your sorted list



Thanks for reading

Rajan verma