colour coded points depending on error amount

[Sapster86]

Hi i'm using excel 2007 (SP1) just wondering is there a way to automatically
assign a different colour to points depending on the difference between the
results on both axises?

The data i'm using is a set of elevations from two different sources that
show the error in an elevation model. I need to ideally make points that say
have a differnce of <50cm from perfect corelation appear a differnt colour to
those which are for example <25cm.

can this be done? any help would be awesome!

I've uploaded the file if anyone wants to take a look:

http://www.filefactory.com/file/26177a/

http://img142.imageshack.us/img142/5...tureou2.th.jpg

 

[Del Cotter]

Hi i'm using excel 2007 (SP1) just wondering is there a way to automatically
assign a different colour to points depending on the difference between the
results on both axises?

The data i'm using is a set of elevations from two different sources that
show the error in an elevation model. I need to ideally make points that say
have a differnce of <50cm from perfect corelation appear a differnt colour to
those which are for example <25cm.

For just a few colours, the solution is relatively easy. Create a new
series using the formula

if difference < 50cm, value, NA()

Create as many series as you need to produce the effect.

 

[Jon Peltier]

Here's an illustrated example of a conditional chart, based not on error but
on value:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon

 

[Sapster86]

Thanks for both your help!

Del Cotter i've got your method working nicely now for highlighting all
values that are within +- 25cm using the formula:
=IF(C4>0.25,NA(),IF(C4<-0.25,NA(),A4))

Where A = first elevation, B = Second elevation & C = difference between the
two.

i've also done this for +- 50, 75, 1 and >1m

the only problem is that it also selects all values before it as well (ie
all the +- 25cm results are also in the +- 50cm table) i'm now trying to get
another formular to seperate these results for me but i'm having some trouble
do you have any ideas?

i'm using these and they don't seem to work:
=IF(AND(C14<0.5,C14>0.25),AND(AND(C14<-0.25,C14<-0.5),"output_1","output_2"),"output_3")
=IF(AND(C14<0.5,C14>0.25),IF(AND(C14>-0.25,C14>-0.5),"output_1","output_2"),"output_3")

i've uploaded the file incase anyone wants to take a look.
http://www.filefactory.com/file/5f8d3a/

many thanks in advance

 

[Del Cotter]

i've got your method working nicely now for highlighting all
values that are within +- 25cm using the formula:
=IF(C4>0.25,NA(),IF(C4<-0.25,NA(),A4))

Where A = first elevation, B = Second elevation & C = difference between the
two.

That's great. I think you can simplify the expression a bit:

=IF(ABS(C4)>0.25,NA(),A4))

ABS() is a function that strips the sign from a number, so that ABS(4)=4
and ABS(-4)=4 as well.

i've also done this for +- 50, 75, 1 and >1m

For a little more flexibility, put the band figures in the first row,
above the data columns, and use

=IF(ABS($C4)>D$1,NA(),$A4))

The dollar signs will keep the references properly anchored, and you can
just copy the formula into columns E, F, G, and H as well without fuss.

the only problem is that it also selects all values before it as well (ie
all the +- 25cm results are also in the +- 50cm table) i'm now trying to get
another formular to seperate these results for me but i'm having some trouble
do you have any ideas?

Using ABS() should make it easier for you to create such a formula. But
to be honest, I wouldn't worry about separating them out, just let each
symbol cover over the ones beneath. To get this to work, you need to
arrange the bands backward. Column D is Column A unaltered (in fact you
can just use column A itself), while columns E-H are now 1.00, 0.75,
0.50 and 0.25, in that order.

Now, with that all said, I have to say I think a better way of showing
the deviations from the centre line might be simply to have a separate
graph underneath the first one plotting the residuals.

i've uploaded the file incase anyone wants to take a look.

I couldn't seem to get that working. See what you think of this version,
based on your email:

http://www.branta.demon.co.uk/excel/Accuracy_Assessment.xls

 

[Sapster86]

Thank you so much you are nothing short of a God! what you've done looks
amazing.

 

[Jon Peltier]

I've done it with a range on my ABS.

=IF(ABS($C4)>D$1,NA(),IF(ABS($C4)<=C$1,NA(),$A4))

- Jon

 

[David Biddulph]

Or presumably =IF(AND(ABS($C4)<=D$1,ABS($C4)>C$1),$A4,NA()) ?