colour coded points depending on error amount
 

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

colour coded points depending on error amount
 

On Wed, 27 Feb 2008, in microsoft.public.excel.charting,
Sapster86 said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

colour coded points depending on error amount
 

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Wed, 27 Feb 2008, in microsoft.public.excel.charting,
Sapster86 said:

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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

colour coded points depending on error amount
 

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(C40.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,C140.25),AND(AND(C14<-0.25,C14<-0.5),"output_1","output_2"),"output_3")
=IF(AND(C14<0.5,C140.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 :)

colour coded points depending on error amount
 

On Thu, 28 Feb 2008, in microsoft.public.excel.charting,
Sapster86 said:
i've got your method working nicely now for highlighting all
values that are within +- 25cm using the formula:
=IF(C40.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/...Assessment.xls

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

colour coded points depending on error amount
 

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

colour coded points depending on error amount
 

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Thu, 28 Feb 2008, in microsoft.public.excel.charting,
Sapster86 said:
i've got your method working nicely now for highlighting all
values that are within +- 25cm using the formula:
=IF(C40.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/...Assessment.xls

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

colour coded points depending on error amount
 

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

"Jon Peltier" wrote in message
...
I've done it with a range on my ABS.

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Thu, 28 Feb 2008, in microsoft.public.excel.charting,
Sapster86 said:
i've got your method working nicely now for highlighting all
values that are within +- 25cm using the formula:
=IF(C40.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/...Assessment.xls

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.