Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
I am trying to visually represent different conditions on a line chart (e.g.
underlying data is rainfall in the UK; plot as a dashed line. The conditions are times when the wind is from the north, times when the temperature is below 10C, and both; plot as different thickness lines on top of the underlying). I've been doing this by calculating when the different conditions are true in diff't columns, returning the underlying data when they are and returning N/As when they're not, then cutting and pasting as values, sorting to delete the N/A's, sorting back by date and displaying as several lines of different formats on top of each other on the chart. Any other ways to do this? if I could leave gaps in lines when the formula value is N/A it would make this much faster, but i can't get it to stop interpolating unless i clear the cells... |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
Bernard -
Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
Agreed but the OP seems to have really empty cells (missing data)
But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
thanks for the prompt responses - unfortunately I have formulas in the empty
cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
Can you change the formula to enter NA() when the plotting data is not
available Show us the formula so we may help you better best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks for the prompt responses - unfortunately I have formulas in the empty cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
As an example, if in column A rows 1-10 i have the values 1, 2...10 etc. In
cell B1 I have a formula =IF(AND(A12,A1<6),A1,NA()) which is then duplicated down to B10. In C1 I have the formula =IF(AND(A16,A1<10),A1,NA()). I want to plot column A as a line chart (dashed for example), and then I want to plot columns B and C as line charts on top of it in different colours, line styles etc to highlight different conditions on the curve. This works fine. The problem is that if the formula in columns B and C are more complicated and select non-contiguous sections of column A then the lines are interpolated. So for instance if the formula in col B is =IF(OR(AND(C172,C17<6),AND(C176,C17<10)),C17,NA( )) then excel draws one line from 3 to 9, rather than two lines going from 3-5 and 7-9. The only way i can stop it interpolating between the two sections is to delete the folrmula, but this isn't practical in spreadsheets with thousands of rows and many columns of conditions. So - I'm looking for a way to leave formulas in all the cells but not have excel join up the lines where there isn't any data. Please let me know if this doesn't make sense. Thanks... "Bernard Liengme" wrote: Can you change the formula to enter NA() when the plotting data is not available Show us the formula so we may help you better best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks for the prompt responses - unfortunately I have formulas in the empty cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
The NA() part of the formulas cause the chart engine to ignore the cell and
joint adjacent points. If you rewrite the formulas in the form =IF(AND(A12,A1<6),A1,"") you will get gaps when the cell displays a blank best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... As an example, if in column A rows 1-10 i have the values 1, 2...10 etc. In cell B1 I have a formula =IF(AND(A12,A1<6),A1,NA()) which is then duplicated down to B10. In C1 I have the formula =IF(AND(A16,A1<10),A1,NA()). I want to plot column A as a line chart (dashed for example), and then I want to plot columns B and C as line charts on top of it in different colours, line styles etc to highlight different conditions on the curve. This works fine. The problem is that if the formula in columns B and C are more complicated and select non-contiguous sections of column A then the lines are interpolated. So for instance if the formula in col B is =IF(OR(AND(C172,C17<6),AND(C176,C17<10)),C17,NA( )) then excel draws one line from 3 to 9, rather than two lines going from 3-5 and 7-9. The only way i can stop it interpolating between the two sections is to delete the folrmula, but this isn't practical in spreadsheets with thousands of rows and many columns of conditions. So - I'm looking for a way to leave formulas in all the cells but not have excel join up the lines where there isn't any data. Please let me know if this doesn't make sense. Thanks... "Bernard Liengme" wrote: Can you change the formula to enter NA() when the plotting data is not available Show us the formula so we may help you better best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks for the prompt responses - unfortunately I have formulas in the empty cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
thanks - but unfortunately using "" instead of NA() as in
=IF(OR(AND(C162,C16<6),AND(C166,C16<10)),C16,"") causes zero values to be displayed on the chart instead of gaps (even when the 'Show Empty Cells' button in 'Hidden & Empty Cells' has been set to 'Gaps') "Bernard Liengme" wrote: The NA() part of the formulas cause the chart engine to ignore the cell and joint adjacent points. If you rewrite the formulas in the form =IF(AND(A12,A1<6),A1,"") you will get gaps when the cell displays a blank best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... As an example, if in column A rows 1-10 i have the values 1, 2...10 etc. In cell B1 I have a formula =IF(AND(A12,A1<6),A1,NA()) which is then duplicated down to B10. In C1 I have the formula =IF(AND(A16,A1<10),A1,NA()). I want to plot column A as a line chart (dashed for example), and then I want to plot columns B and C as line charts on top of it in different colours, line styles etc to highlight different conditions on the curve. This works fine. The problem is that if the formula in columns B and C are more complicated and select non-contiguous sections of column A then the lines are interpolated. So for instance if the formula in col B is =IF(OR(AND(C172,C17<6),AND(C176,C17<10)),C17,NA( )) then excel draws one line from 3 to 9, rather than two lines going from 3-5 and 7-9. The only way i can stop it interpolating between the two sections is to delete the folrmula, but this isn't practical in spreadsheets with thousands of rows and many columns of conditions. So - I'm looking for a way to leave formulas in all the cells but not have excel join up the lines where there isn't any data. Please let me know if this doesn't make sense. Thanks... "Bernard Liengme" wrote: Can you change the formula to enter NA() when the plotting data is not available Show us the formula so we may help you better best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks for the prompt responses - unfortunately I have formulas in the empty cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
Would you like to send me a sample file
Please get my email address from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks - but unfortunately using "" instead of NA() as in =IF(OR(AND(C162,C16<6),AND(C166,C16<10)),C16,"") causes zero values to be displayed on the chart instead of gaps (even when the 'Show Empty Cells' button in 'Hidden & Empty Cells' has been set to 'Gaps') "Bernard Liengme" wrote: The NA() part of the formulas cause the chart engine to ignore the cell and joint adjacent points. If you rewrite the formulas in the form =IF(AND(A12,A1<6),A1,"") you will get gaps when the cell displays a blank best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... As an example, if in column A rows 1-10 i have the values 1, 2...10 etc. In cell B1 I have a formula =IF(AND(A12,A1<6),A1,NA()) which is then duplicated down to B10. In C1 I have the formula =IF(AND(A16,A1<10),A1,NA()). I want to plot column A as a line chart (dashed for example), and then I want to plot columns B and C as line charts on top of it in different colours, line styles etc to highlight different conditions on the curve. This works fine. The problem is that if the formula in columns B and C are more complicated and select non-contiguous sections of column A then the lines are interpolated. So for instance if the formula in col B is =IF(OR(AND(C172,C17<6),AND(C176,C17<10)),C17,NA( )) then excel draws one line from 3 to 9, rather than two lines going from 3-5 and 7-9. The only way i can stop it interpolating between the two sections is to delete the folrmula, but this isn't practical in spreadsheets with thousands of rows and many columns of conditions. So - I'm looking for a way to leave formulas in all the cells but not have excel join up the lines where there isn't any data. Please let me know if this doesn't make sense. Thanks... "Bernard Liengme" wrote: Can you change the formula to enter NA() when the plotting data is not available Show us the formula so we may help you better best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mick perry" wrote in message ... thanks for the prompt responses - unfortunately I have formulas in the empty cells so the "Hidden and Empty Cells" instruction still interpolates between the values (for my chart to work I need to leave gaps in the lines on the charts, so that you can see which sections of the underlying data fit the conditions and which ones don't) "Bernard Liengme" wrote: Agreed but the OP seems to have really empty cells (missing data) But I should have added the formula proviso for the sake of others Cheers -- Bernard "Jon Peltier" wrote in message ... Bernard - Don't forget that the "Empty Cells" setting applies only to empty cells, not cells containing "" or #N/A. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Have your tried enter =NA() in the blank cells? This displays as #N/A which the chart engine ignores. Also there is a chart option to specify that blanks are to be ignored and points are to be joined. In XL2003; click chart; open Tools | Options | Chart and locate area specifying how blanks are to be treated In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of dialog box "Hidden and Empty Cells" best wishes |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
Hi,
The use of NA() only removes the data markers it will not cause a break in the line. The line will be interpolated between any data points that have a numeric value. You can see the variations here. http://www.andypope.info/charts/brokenlines.htm Cheers Andy Bernard Liengme wrote: The NA() part of the formulas cause the chart engine to ignore the cell and joint adjacent points. If you rewrite the formulas in the form =IF(AND(A12,A1<6),A1,"") you will get gaps when the cell displays a blank best wishes -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
plotting line charts with formula based 'gaps'
That still won't provide a gap...
- Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Bernard Liengme wrote: Can you change the formula to enter NA() when the plotting data is not available Show us the formula so we may help you better best wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to auto-fill in gaps between numbers on straight line bas | Excel Worksheet Functions | |||
Gaps in Line Chart | Charts and Charting in Excel | |||
plotting charts | Charts and Charting in Excel | |||
Plotting in Charts | Excel Discussion (Misc queries) | |||
Plotting blank cells in line charts? | Charts and Charting in Excel |