Geographical heat map

[Xen]

So, I have this cool idea of creating dynamic geographical heat map in excel:

I know that excel is not really suitable for this, but I've got basic implementation and it is working perfectly. You drag slider and see how things are changing over time.

The problems, I want to have gradient (in case areas intersect, you will see some really good hot spots), and actual map is going to be a lot bigger, so I will have to use smaller cell size. In current implementation, I 'blur' numbers by averaging area around if cell in original table equals to zero, or use existing value if present. So, if I want more blur, I have to copy map over and over until I get large enough area covered. Current result (see picture above) is achieved by copying first iteration three more times (see file attached).

Current implementation is all I could think of. I bet there is a better way, that probably uses arrays or something similar, but sadly, this is not my strong point.

So, I am looking for one of these:
1. Way to 'blur' my numbers in one iteration
2. Or overall better solution to achieve same result

Please note, that dragging slider updates table in real time, say, charts won't update until you release slider (I had version with ordinary chart that represented change over time with straight vertical error bar crossing whole chart that appeared exactly on spot selected now by slider, sadly enough, it wouldn't update unless you release slider, so I had to abandon this idea), so I need a solution that updates as you drag.

Any ideas are appreciate

 

[Hui]

A Few questions from above
If you use an ActiveX Slider rather than a Form Control you will get smoother control that updates as you drag
You will need a small piece of VBA Code like below in the Sheet1 Code module:

Private Sub ScrollBar1_Change()
  [B1] = ScrollBar1.Value
End Sub

I am unsure what you mean by Blur?
The blurring happens in the averaging of the surrounding cells in the W5:AL27 calculation area

 

[Xen]

ActiveX Slider

Does it work with Mac version of excel? At least one of my recipients uses Mac, and I was convinced that ActiveX is windows thing.

I am unsure what you mean by Blur?

Gradually smaller numbers around value, like if I enter 2000 into one empty cell, area around it would look like this in final result when color scale conditional formatting is applied:

500 500 500 500 500
500 1000 1000 1000 500
500 1000 2000 1000 500
500 1000 1000 1000 500
500 500 500 500 500

In reality, numbers on data grid will by dynamic, there will be more smaller and smaller numbers around value, and they can be close together, so sometime areas would intersect like this:
100 100 100
100 200 100
100 100 150 100 100
..............100 200 100
...............100 100 100
I currently achieve this by averaging 3x3 area around value in original table, if I want more smooth transition, I have to 'average averaged' table, and if I need even more smooth result... well, the more smooth and detailed result I want, the more times I'd have to copy map over and over, which ideally I'd like to avoid.

 

[Xen]

I'm uploading example of 'blurring' table, that is achieved by copying table over and over

 

[Hui]

ActiveX components don't work on Macs, Correct

Which of the Tables do you want the numbers blurred?

 

[Xen]

In my first example, it's first table (one that says Data)
Basic data look like this square area with few numbers scattered around it like:
........100.............300..........
......200...............................
...........................................
................................300.....
................140.....................
333........................220.......
...........................................

You can take a look at original file (heatmap.zip), it has sample data and final result that I got after averaging same table 4 times. In final result table, numbers are hidden with ;;; format

 

[Hui]

Couldn't you just add several manual levels of Conditional Formatting ?
If > 20,000 Red Stop
If > 15,000 Orange Stop
If > 10,000 Yellow Stop
If > 5,000 Green Stop
If > 0 White

 

[Xen]

It's not formatting that it the problem, color scales work just fine:

Problem is, I have large grid and not so much samples of data points, so to make map more obvious, I want to every spot to have gradient around it (fading from red to yellow depending on value). As you can see on picture above, transitions are very smooth and intersections are connected (I've attached this file earlier, blue.xlsx), so we get really nice heat map in the end. Problem is that this is achieved by copying area five times. And I want to avoid this, because I'd want to have maps of several areas and each is going to be more detailed, which I'd have to make dozens of copies to achieve good looking result.

I'm attaching another sample, smaller area with numbers in every table.

 

[Hui]

I'm not totally convinced of the merits of Averaging 3 times, but hey it may work

What I'd suggest is using an =Average(Offset()) function like: =AVERAGE(OFFSET(P32,-$AG$28,-$AG$29,2*$AG$28+1,2*$AG$29+1))

This allows you to set a Row & Column offset and see what happens
You need to ensure you have enough 0's around your data for the edges to be calculated correctly

See attached example