• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is this Chart type Possible in Excel?

J

JJ Van niel

New Member
Hello all,

I am trying to make a heatmap based on basketball shot areas. In excel I know you can use conditional formatting to make one that is based on the cells but the chart I have attached is what I am looking for. I'm guessing the chart would have to be done in VBA which is fine and maybe even using objects not a chart.

Essentially, every time a shot is taken from an area that area gets hotter.

Any help on this is greatly appreciated. I have attached an example of what I am trying to do.

Best
 

Attachments

  • HEATMAP.png
    HEATMAP.png
    124 KB · Views: 25
Hi ,

If you can upload your data , that may make it easier for others to take a shot at developing the chart.

Narayan
 
Ok, the data are just points representing where the shot was taken from. Hers is a description of how they work.

The coordinates of each point are broken down as follows first two digits are the distance from the baseline (basket). 51 is about the baseline all the way out to 90 which is past the 3pt line. The 2nd two digits correspond to the side of the court. So if you are facing the basket the right sideline is 11 and the left sideline is 89.
Here are some examples:
5111 you get a baseline three point shot from the right side.
5189 is a baseline shot from the left side.
8045 or so would be a shot from the top of the key

These are not exact as I am doing this off the top of my head but you can ignore the court and just make it a square chart and create the heat map based on the data points in this sheet.

Thank you for your help.
 

Attachments

  • Heatmap Data.xlsx
    10.6 KB · Views: 20
@JJ Van niel
If left isn't left or top isn't top, it can always change.
Needs a lot of data ... copy to column 'A'.
Press the [button]
Something like this?
 

Attachments

  • Heatmap Data.xlsb
    17.1 KB · Views: 16
There are many ways to approach this. The attached isn't as I originally intended but I got there so fast I thought it might be worth posting.
There's a little UDF in there to calculate the number of nearest neighbours for each point. It's a bit slow but it could be made much faster by converting it to an array-entered user-defined function. I used Text-to-columns to split your values. Play with the spin boxes.
As an afterthought I added a semi-transparent image - I've no idea if the orientation, size or positioning are right.
It could be a starting point.
heatmap2.jpg
 

Attachments

  • chandoo27224Heatmap Data.xlsm
    47.2 KB · Views: 15
Last edited:
This is a great start and I should be able to get there from this. Thank you very much!

p45cal said:
There are many ways to approach this. The attached isn't as I originally intended but I got there so fast I thought it might be worth posting.
There's a little UDF in there to calculate the number of nearest neighbours for each point. It's a bit slow but it could be made much faster by converting it to an array-entered user-defined function. I used Text-to-columns to split your values. Play with the spin boxes.
As an afterthought I added a semi-transparent image - I've no idea if the orientation, size or positioning are right.
It could be a starting point.
View attachment 25773
Click to expand...
 
As I am playing with this, I'm curious if there is a way to get other colors based on the frequency. Ie. lets say there are just 5 shots in an area at the top corner of the three point line and there are 50 from the bottom corner of the three point line and another 25 right in the middle of the three point line. So maybe there is a rule that less than 25% of the total shots are colored light blue, 25%-49% are colored yellow while 50% plus are colored red. It would have to be based on some range around a point so maybe it will be too complicated.


p45cal said:
There are many ways to approach this. The attached isn't as I originally intended but I got there so fast I thought it might be worth posting.
There's a little UDF in there to calculate the number of nearest neighbours for each point. It's a bit slow but it could be made much faster by converting it to an array-entered user-defined function. I used Text-to-columns to split your values. Play with the spin boxes.
As an afterthought I added a semi-transparent image - I've no idea if the orientation, size or positioning are right.
It could be a starting point.
View attachment 25773
Click to expand...
 
@JJ Van niel
This needs data, so far max 'shots' in one place is ~two (100*100-scala).
Of course, if 10*10-scala is okay, there would be more 'shots'.
'my version' has now 'max' three colors ... maybe only two needs to use.
 
The attached file is the similar to my previous file but with the addition of a couple of sheets being the approach I originally intended to have.
This is to take each point in the 100x100 grid, count how many shots were taken within a certain (userdefinable) distance from that point, then conditionally format the colour of a cell representing that point according to that count.
Sheets 2 and 3 do this but in slightly different ways with sheet3 being quicker to recalculate as it uses a single array formula for the whole grid.
The cells in the grids each have the count but this is hidden by a custom format (the text interfered with the colouring). There is also a conditional format applied to the whole grid which you can tweak yourself to change the colours etc.
The orientation may not be right and the patterns showing may be a mirror image of what it should be - this is just proof-of-concept rather than a final solution.

As an aside, if the purpose of this is to help a team with its game stratgey, it might be more useful if each shot's data also included success/failure, as currently we're just showing where shots are taken from. So while the chart may show that there may be many shots from one particular area of the pitch/court, if they're 100% failed shots then you can tell the team how fruitless it is taking shots from there! If on the other hand, there is a smaller cluster of shots from another area of the pitch but they're 100% successful, then you might be able to tell your team to shoot from there more often!

If I get time, I'll look into programmatically changing the colours of the bubbles in the chart on Sheet1.
 

Attachments

  • chandoo27224Heatmap Data04.xlsm
    182.7 KB · Views: 14
I think the chart seems cleaner to implement given how long the others take to recalculate. This will be part of a dashboard type workbook with a bunch of data and other calculations in it so the newest solutions with conditional formatting may prove inefficient for recalculations and with all the data.

If there is a away to show success in addition to density that would be amazing.

For the coloring, I'm wondering if maybe there are just three series of bubble charts for each corresponding density and once the data is it it only fills in the series that corresponds to the density. So there are three columns one for each series (Blue series, Yellow Series, Red Series) the formula in blue is something like if(cell value <25% then cell value, else 0), yellow would be if(AND(cell value >=25%, cell value <50%), then cell value, else 0) etc...

Thanks for all the help on this.

p45cal said:
The attached file is the similar to my previous file but with the addition of a couple of sheets being the approach I originally intended to have.
This is to take each point in the 100x100 grid, count how many shots were taken within a certain (userdefinable) distance from that point, then conditionally format the colour of a cell representing that point according to that count.
Sheets 2 and 3 do this but in slightly different ways with sheet3 being quicker to recalculate as it uses a single array formula for the whole grid.
The cells in the grids each have the count but this is hidden by a custom format (the text interfered with the colouring). There is also a conditional format applied to the whole grid which you can tweak yourself to change the colours etc.
The orientation may not be right and the patterns showing may be a mirror image of what it should be - this is just proof-of-concept rather than a final solution.

As an aside, if the purpose of this is to help a team with its game stratgey, it might be more useful if each shot's data also included success/failure, as currently we're just showing where shots are taken from. So while the chart may show that there may be many shots from one particular area of the pitch/court, if they're 100% failed shots then you can tell the team how fruitless it is taking shots from there! If on the other hand, there is a smaller cluster of shots from another area of the pitch but they're 100% successful, then you might be able to tell your team to shoot from there more often!

If I get time, I'll look into programmatically changing the colours of the bubbles in the chart on Sheet1.
Click to expand...
 
This needs to be something where it would work with only 25 shots so the needs more data has to be solved somehow. I think there needs to be a way to calculate the number of points within a certain distance from the point to create the clusters.


vletm said:
@JJ Van niel
This needs data, so far max 'shots' in one place is ~two (100*100-scala).
Of course, if 10*10-scala is okay, there would be more 'shots'.
'my version' has now 'max' three colors ... maybe only two needs to use.
Click to expand...
 
@JJ Van niel
1st You asked something like HEATMAP.png, for that, it needs more data.
For example, if You have many ~25 dataset including hit or no.
You could see quickly each of those one by one with one click...
If You could tell, what data do You want to use? ... and what else?
It would be easier to get Your the whole idea.
Of course, this is possible to do step-by-step, but many times that means 'two step forward - one step backward (sometimes three steps).
 
JJ Van niel said:
I think the chart seems cleaner to implement given how long the others take to recalculate. This will be part of a dashboard type workbook with a bunch of data and other calculations in it so the newest solutions with conditional formatting may prove inefficient for recalculations and with all the data.
Click to expand...
Personally, I don't think the chart is a good idea as it so easily hides data from the user with its overlapping bubbles, regardless of how transparent they are.


JJ Van niel said:
If there is a away to show success in addition to density that would be amazing.
Click to expand...
A way to do this is to have a separate column with the source data showing success/failure. it could be an x, a 1, TRUE if a hit and nothing if a miss. I would have separate grids showing each of Hits,Misses and All, or a way to toggle through the 3 on just one grid.

JJ Van niel said:
For the coloring, I'm wondering if maybe there are just three series of bubble charts for each corresponding density and once the data is it it only fills in the series that corresponds to the density. So there are three columns one for each series (Blue series, Yellow Series, Red Series) the formula in blue is something like if(cell value <25% then cell value, else 0), yellow would be if(AND(cell value >=25%, cell value <50%), then cell value, else 0) etc...
Click to expand...
In sheet 3 of the attached you can set this sort of thing as you like.

The other sheets in the attached are a variation of what was there before but with a few more tweaks available.
 

Attachments

  • chandoo27224Heatmap Data06.xlsm
    176.8 KB · Views: 31
OK, got it. Thanks for all the help. I definitely have some great options to worth with now.

p45cal said:
Personally, I don't think the chart is a good idea as it so easily hides data from the user with its overlapping bubbles, regardless of how transparent they are.


A way to do this is to have a separate column with the source data showing success/failure. it could be an x, a 1, TRUE if a hit and nothing if a miss. I would have separate grids showing each of Hits,Misses and All, or a way to toggle through the 3 on just one grid.

In sheet 3 of the attached you can set this sort of thing as you like.

The other sheets in the attached are a variation of what was there before but with a few more tweaks available.
Click to expand...
 
You must log in or register to reply here.
Back
Top