Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 :) |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
colour coded points depending on error amount
Thank you so much you are nothing short of a God! what you've done looks
amazing. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print depending on tab colour | Excel Discussion (Misc queries) | |||
colour of a row depending on cell value | Excel Discussion (Misc queries) | |||
Changing Colour of Bar depending on Value | Charts and Charting in Excel | |||
calculating different percentages depending on amount | Excel Discussion (Misc queries) | |||
rating cells 1-5 colour coded HOW? | New Users to Excel |