Hi All,


I have created a chart the source data contain cell having formulas which contain

IF('HR EXP VOL'!G10+'CRCA EXP VOL'!G10+'GALV EXP VOL'!G10=0,"",'HR EXP VOL'!G10+'CRCA EXP VOL'!G10+'GALV EXP VOL'!G10)

that is if the value of sum is 0 then display Blank.
its working fine. cell having value 0 is displayed as blank. but the problem is in place of blank 0 is shown in the chart.

Pl help me out :(



It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

__________________
It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

It must be a numeric format in the chart so the Null setting results in a value of 0. You could try Tools->Options, View tab and uncheck "Show zero values" although this has it's side effects in other cells. You might also try " " instead of "" just to see what happens.

MagicTH thanks for reply.

I tried your both solutions, But it's not working.

Pl give me some other solution.

It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

The following replaces the "" or zero with a null value and omits it, however it does join the two points either side (in the case of a line graph.

IF('HR EXP VOL'!G10+'CRCA EXP VOL'!G10+'GALV EXP VOL'!G10=0,#n/a,'HR EXP VOL'!G10+'CRCA EXP VOL'!G10+'GALV EXP VOL'!G10)

cheers

Matt

Thanks Matt,

I have already tried this #N/A option it is working fine. but the problem is that I am calculating the sum of all the cell that contains the above mention formula. now if the #N/A is displayed in the cells chart is not showing the value which I want, but the sum formula is dispalying #N/A which is again a problem.

I have given the sum formula =SUM(G8:G19)
cells G8 to G19 all contains the above formula. now if the #N/A is displayed I want the sum of all the numbers only, but it is returning #N/A.



It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

rather than using the sum function use the following function still using the #n/a representation for the graph:-

=SUMIF(G8:G19,"<>#N/A",G8:G19)

cheers

Matt

Thanks alot Matt

It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

Hi Matt,

Again need your help.

As per you suggestion I m using #N/A in formula.
now the problem is I want to access the value of the cell in order to compare the values. when I am trying to read value of the cell in which #N/A is displayed it is giving error. I want the value as 0 if the cell contain #N/A.


Pl help.


It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha

Hi Rekha,

This follows the same kind of line as the previous postings, if you are evaluating a single cell and there is apossibility that it will have an #n/a result, then you would be best using the following formula...

=IF(ISNA(B1),0,B1)

Hope this helps.

Cheers

Matt

Once again Thanks Matt

It is always safe to assume, not that the old way is wrong, but that there may be a better way...
*************************
Rekha