Color Weighting

[Guest]

I designed an RF signal strength mapping tool for our radio shop so they can map the relative repeater signal strengths around the plant. Basicly it is a laptop with a National Instruments DAQ board and a Garmin GPS head connected via RS232. Signal strength is fed into the DAQ board from a modified reciever. The output is a tab delimited text file that has 3 columns. GPS Lat & long and signal strength in dBm. My question to you Excel experts is... Can a chart be produced that would have Lat and Long as XY coordinates with each signal strength data point represented by a dot that varies in color proportinal to the numeric value? I could do it in AutoCAD but it would be nice if one of you Excel junkies can tell me a way to do it in this program..

Thanks in advance
Ed Nauma
Senior Enginee
Lockheed Martin Aeronautic
Palmdale, CA

 

[Nicky]

This is difficult because you can’t set the colors of a chart marker a
precisely as you could, say, a drawing object (eg circle or box). Yo
can only apply one of the set scheme colors. However, you can alte
these scheme colours in tools! options.

I could think of three partial solutions to your problem:

1. split your data into several different series according to signa
strengths. I have no idea what units or likely magnitudes these woul
show, but assuming values range from 0 to 100, you could have on
series for 0-25, another for 26-50 etc, each with different color dots


2. use a macro like this to give different points of a single serie
different scheme colors. Again, as above, these are collected int
groups, not fully variable according to the magnitude of the data. Th
values for signal strength are assumed to be in a range name
‘strength’. As mentioned above, you can alter the scheme colors i
tools! options.

note that both of these macros also add the signal stregth value as
data label to your markers
Sub vary__color_by_value()
Calculate
LUM_MAX = 100 'HIGHEST STRENGTH VALUE POSSIBLE
PT_MAX = 50 ' LARGEST SIZE OF POINT IN SCATTER CHART

For n = 1 To Range("STRENGTH").Cells.Count
pct = 10 + Round(10 * (0.05 + Range("STRENGTH").Cells(n).Value
LUM_MAX), 0) 'ratio of point size to maximum
ActiveChart.SeriesCollection(1).Points(n).Select
With Selection
.MarkerBackgroundColorIndex = pct
.MarkerForegroundColorIndex = pct
.MarkerStyle = xlCircle
.MarkerSize = 15
.Shadow = False
.DataLabel.Characters.Text
Sheets("SHEET1").Range("STRENGTH").Cells(n).Value
End With

Next
End Sub

3) rather than using color to differentiate the signal strength, wh
not use the size of the chart marker? This can be calibrated mor
precisely and maybe read more easily than scheme colors. This should d
the trick:

Sub vary__size_by_value()
Calculate
LUM_MAX = 100 'HIGHEST STRENGTH VALUE POSSIBLE
PT_MAX = 50 ' LARGEST SIZE OF POINT IN SCATTER CHART
For n = 1 To Range("STRENGTH").Cells.Count
pct = Range("STRENGTH").Cells(n).Value / LUM_MAX 'ratio of point siz
to maximum
If pct < 0.1 Then pct = 0.1 'minimum point size is 10% maximum
ActiveChart.SeriesCollection(1).Points(n).MarkerSize = pct * PT_MAX
ActiveChart.SeriesCollection(1).Points(n).DataLabel.Characters.Text
Sheets("SHEET1").Range("STRENGTH").Cells(n).Value
Next
End Su

 

[Jon Peltier]

Carrying Nicky's suggestion a bit further, you can in fact make custom
markers with colors showing what you're interested in.

First, you need your three columns, just like you have. Plot an XY
Scatter chart with lat and long. Now step through each point, create a
drawing object (circle or whatever) the right size, and fill it with a
custom color based on signal strength. Copy it, then select the series
and point, and paste. The drawing object becomes a custom marker for
that point.

Turn on the macro recorder while creating and formatting the drawing
object, and while copying it, selecting the point, and pasting. This
gives you the syntax you need.

I used this procedure once to plot an array of arrows, with variable
orientation and length, to depict mass transfer on a chart. Line color
could have conveyed yet another variable.

- Jon