|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi.
I am making a model in Excel which is takes data updates from an outside
source and then processes the information to develop charts on the data.
There are a lot of charts so I have developed the model to process the data
automatically as the data comes (down the sheet). This processing means that
there are formulas in place, waiting for the data to come in. I use a
=if(a1="","",<operation>) notation to do it for me. If there is data then
the if statement is false and the data can be processed; if the data hasn't
arrived yet then instead of getting an error message you get a "".
This works fine and I have set up the graphs so that there is a little
leeway for additional data to come in and for the information to fit in the
array read by the chart. However, my problem is that when the data hasn't
arrived yet and Excel encounters "" in a cell (i.e., the if statement is
true), which to me means <blank> or <empty>, it reads it as a zero.
Therefore my nice charts have a large vertical line back down to zero at the
end of every data series.
One option is to redo the arrays of the chart whenever I use the model but
there are probably 200 charts so this is uneconomic! What I am looking for
is a way to deal with the formulas so the Excel reads my "" as <empty> rather
than a zero. Has anyone come across this problem before? Does anyone know
how I might solve this problem?
Thanks for your help.
Tom
|
|
Hi Thomas
Instead of using "" in your formulae to return null values, use NA()
Excel will ignore the #N/A in a cell when it comes to graphing
--
Regards
Roger Govier
"ThomasStudd" <ThomasStudd[ at ]discussions.microsoft.com> wrote in message
news:3400233D-BC14-41A0-9725-4C2C3FFC6BC0[ at ]microsoft.com...
[Quoted Text] > Hi.
>
> I am making a model in Excel which is takes data updates from an
> outside
> source and then processes the information to develop charts on the
> data.
> There are a lot of charts so I have developed the model to process the
> data
> automatically as the data comes (down the sheet). This processing
> means that
> there are formulas in place, waiting for the data to come in. I use a
> =if(a1="","",<operation>) notation to do it for me. If there is data
> then
> the if statement is false and the data can be processed; if the data
> hasn't
> arrived yet then instead of getting an error message you get a "".
>
> This works fine and I have set up the graphs so that there is a little
> leeway for additional data to come in and for the information to fit
> in the
> array read by the chart. However, my problem is that when the data
> hasn't
> arrived yet and Excel encounters "" in a cell (i.e., the if statement
> is
> true), which to me means <blank> or <empty>, it reads it as a zero.
> Therefore my nice charts have a large vertical line back down to zero
> at the
> end of every data series.
>
> One option is to redo the arrays of the chart whenever I use the model
> but
> there are probably 200 charts so this is uneconomic! What I am
> looking for
> is a way to deal with the formulas so the Excel reads my "" as <empty>
> rather
> than a zero. Has anyone come across this problem before? Does anyone
> know
> how I might solve this problem?
>
> Thanks for your help.
>
> Tom
>
|
|
Change your formula from =if(a1="","",<operation>) to
=if(a1=NA(),"",<operation>)
Now the blanks become #N/A! which the chart engine ignores
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"ThomasStudd" <ThomasStudd[ at ]discussions.microsoft.com> wrote in message
news:3400233D-BC14-41A0-9725-4C2C3FFC6BC0[ at ]microsoft.com...
[Quoted Text] > Hi.
>
> I am making a model in Excel which is takes data updates from an outside
> source and then processes the information to develop charts on the data.
> There are a lot of charts so I have developed the model to process the
> data
> automatically as the data comes (down the sheet). This processing means
> that
> there are formulas in place, waiting for the data to come in. I use a
> =if(a1="","",<operation>) notation to do it for me. If there is data then
> the if statement is false and the data can be processed; if the data
> hasn't
> arrived yet then instead of getting an error message you get a "".
>
> This works fine and I have set up the graphs so that there is a little
> leeway for additional data to come in and for the information to fit in
> the
> array read by the chart. However, my problem is that when the data hasn't
> arrived yet and Excel encounters "" in a cell (i.e., the if statement is
> true), which to me means <blank> or <empty>, it reads it as a zero.
> Therefore my nice charts have a large vertical line back down to zero at
> the
> end of every data series.
>
> One option is to redo the arrays of the chart whenever I use the model but
> there are probably 200 charts so this is uneconomic! What I am looking
> for
> is a way to deal with the formulas so the Excel reads my "" as <empty>
> rather
> than a zero. Has anyone come across this problem before? Does anyone
> know
> how I might solve this problem?
>
> Thanks for your help.
>
> Tom
>
|
|
Many thanks to you both.
Tom
"Bernard Liengme" wrote:
[Quoted Text] > Change your formula from =if(a1="","",<operation>) to
> =if(a1=NA(),"",<operation>)
> Now the blanks become #N/A! which the chart engine ignores
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "ThomasStudd" <ThomasStudd[ at ]discussions.microsoft.com> wrote in message
> news:3400233D-BC14-41A0-9725-4C2C3FFC6BC0[ at ]microsoft.com...
> > Hi.
> >
> > I am making a model in Excel which is takes data updates from an outside
> > source and then processes the information to develop charts on the data.
> > There are a lot of charts so I have developed the model to process the
> > data
> > automatically as the data comes (down the sheet). This processing means
> > that
> > there are formulas in place, waiting for the data to come in. I use a
> > =if(a1="","",<operation>) notation to do it for me. If there is data then
> > the if statement is false and the data can be processed; if the data
> > hasn't
> > arrived yet then instead of getting an error message you get a "".
> >
> > This works fine and I have set up the graphs so that there is a little
> > leeway for additional data to come in and for the information to fit in
> > the
> > array read by the chart. However, my problem is that when the data hasn't
> > arrived yet and Excel encounters "" in a cell (i.e., the if statement is
> > true), which to me means <blank> or <empty>, it reads it as a zero.
> > Therefore my nice charts have a large vertical line back down to zero at
> > the
> > end of every data series.
> >
> > One option is to redo the arrays of the chart whenever I use the model but
> > there are probably 200 charts so this is uneconomic! What I am looking
> > for
> > is a way to deal with the formulas so the Excel reads my "" as <empty>
> > rather
> > than a zero. Has anyone come across this problem before? Does anyone
> > know
> > how I might solve this problem?
> >
> > Thanks for your help.
> >
> > Tom
> >
>
>
>
|
|
|