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

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

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

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.

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.