Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line chart with zerovalues
hi everyone
another question concerning my line chart: i have a series which reach from A4 to A20, the first and the last cells are empty, excel makes en empty space in the Chart, this would be the perfect solution, but I changed the Cells to contain a formula, now excel interprets the cell as 0 which gives me an ugly line at the beginning and the end of the chart. i found this (foreign) forum-entry which talks about this issue: http://experts.about.com/q/Excel-105...pty-values.htm now, my problem goes further: i have some MAX and MIN calculations on these cells, with the #N/A the MIN and the MAX statements are producing only #N/A! Is there some workaround? or some solution? Or do i have to calculate the MIN and the MAX statement with a diffrent row? Cheers and thanks for your Time Carlo |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line chart with zerovalues
It would help if you shared the formula which isn't working.
In general it should look like =IF(<is it a valid calculation, <calculation, NA()) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Carlo" wrote in message ... hi everyone another question concerning my line chart: i have a series which reach from A4 to A20, the first and the last cells are empty, excel makes en empty space in the Chart, this would be the perfect solution, but I changed the Cells to contain a formula, now excel interprets the cell as 0 which gives me an ugly line at the beginning and the end of the chart. i found this (foreign) forum-entry which talks about this issue: http://experts.about.com/q/Excel-105...pty-values.htm now, my problem goes further: i have some MAX and MIN calculations on these cells, with the #N/A the MIN and the MAX statements are producing only #N/A! Is there some workaround? or some solution? Or do i have to calculate the MIN and the MAX statement with a diffrent row? Cheers and thanks for your Time Carlo |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line chart with zerovalues
Hi Jon
well, this is the formula which is in my "range"-cells: =IF(OR(ROW()<MAX($H$5;$H$1);X7="");#N/A;100+(X7-INDIRECT("x" & MAX($H$5;$H$1)))/INDIRECT("x" & MAX($H$5;$H$1))*100) I need the #N/A, otherwise these points would show up on my Chart with zero! If there would be another solution then the #N/A that would be great, otherwise i leave it like that. I can work around, it's a lil bit more work, but should function anyways. Thanks for your answer Carlo "Jon Peltier" wrote: It would help if you shared the formula which isn't working. In general it should look like =IF(<is it a valid calculation, <calculation, NA()) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Carlo" wrote in message ... hi everyone another question concerning my line chart: i have a series which reach from A4 to A20, the first and the last cells are empty, excel makes en empty space in the Chart, this would be the perfect solution, but I changed the Cells to contain a formula, now excel interprets the cell as 0 which gives me an ugly line at the beginning and the end of the chart. i found this (foreign) forum-entry which talks about this issue: http://experts.about.com/q/Excel-105...pty-values.htm now, my problem goes further: i have some MAX and MIN calculations on these cells, with the #N/A the MIN and the MAX statements are producing only #N/A! Is there some workaround? or some solution? Or do i have to calculate the MIN and the MAX statement with a diffrent row? Cheers and thanks for your Time Carlo |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line chart with zerovalues
On Tue, 5 Sep 2006, in microsoft.public.excel.charting,
Carlo said: well, this is the formula which is in my "range"-cells: =IF(OR(ROW()<MAX($H$5;$H$1);X7="");#N/A;100+(X7-INDIRECT("x" & MAX($H$5;$H$1)))/INDIRECT("x" & MAX($H$5;$H$1))*100) I need the #N/A, otherwise these points would show up on my Chart with zero! If there would be another solution then the #N/A that would be great, Ask on the Worksheet Functions forum (microsoft.public.excel.worksheet. functions) about using array formulas to mimic a "MAXIF" type function. This should let you keep using N/A in your column, but allow the MAX function to ignore them. -- 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Line chart with zerovalues
Hi Del
thanks for your answer. They already gave me an answer! Thanks to all Carlo "Del Cotter" wrote: On Tue, 5 Sep 2006, in microsoft.public.excel.charting, Carlo said: well, this is the formula which is in my "range"-cells: =IF(OR(ROW()<MAX($H$5;$H$1);X7="");#N/A;100+(X7-INDIRECT("x" & MAX($H$5;$H$1)))/INDIRECT("x" & MAX($H$5;$H$1))*100) I need the #N/A, otherwise these points would show up on my Chart with zero! If there would be another solution then the #N/A that would be great, Ask on the Worksheet Functions forum (microsoft.public.excel.worksheet. functions) about using array formulas to mimic a "MAXIF" type function. This should let you keep using N/A in your column, but allow the MAX function to ignore them. -- 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 | |||
Bar Chart To Line Chart for last 3 points only | Charts and Charting in Excel | |||
tick mark labels | Charts and Charting in Excel | |||
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
Line chart in Excel - trendline incomplete | Charts and Charting in Excel | |||
Line chart - date line association gone mad! | Charts and Charting in Excel |