Suppressing negative value warning on Log plots
Charles V. Stancampiano
constantly when you have a negative number in a range plotted on a Log
scale? I can fudge the data to remove the negative result, but I would
rather just get rid of the message. Thanks.
Tushar Mehta
thought it would be an appropriate reminder. I haven't figured out a
way to get rid of the warning. While it would be nice to suppress the
warning on occasion, most of the time I would rather see the warning
when I'm plotting inappropriate values than plot illogical values and
not be warned ;-)
Just as I prefer seeing DIV/0 and N/A errors rather than hiding them
will stupid formulas or conditional formatting tricks.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <sK7s7.552302$T97.73...@typhoon.nyroc.rr.com>, Charles V.
Stancampiano <cv...@zdnetmail.com> wrote
Charles V. Stancampiano
(to me since <0 values are "noise"). I don't mind being warned once, but it
does get tiresome.
The data is being written into Excel via DDE from another in-house
application. I would like to write a blank cell into Excel when the data in
invalid so that it overwrites old data, but I can't get it to poke in a
blank cell. If I write a "" the cell appears as NULL. If I write in =NA(),
then the data has #NA in it and the AVERAGE() function doesn't work.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.161ad75a7...@msnews.microsoft.com...
Tushar Mehta
Something like:
If <legit data> then <cell reference>.Value=<some value>
In the alternative, do *nothing.* In fact, if the cell already contains
something, clear the contents with the Clear method of the Range object.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <rmcs7.554293$T97.73...@typhoon.nyroc.rr.com>, Charles V.
Micky Bitsco
decent solution for in Excel.
The problem seems to be more severe in Excel 2000 than older versions of Excel.
It's a huge annoyance that other than using Lotus or writing a charting program
with standalone VB that I have complete control over, there is no solution for.
I find it very curious of MS programmers to insist on these error message
popping in with log plots and no way of easily turning them off. It's very very
common in the real engineering and financial world to have data that is not
continuous over time whose most applicable way of dealing with it, is to ignore
the data without screwing up the time plot. I think it shows some naiveté on
MS's part.
"Charles V. Stancampiano" <cv...@zdnetmail.com> wrote in message
news:rmcs7.554293$T97.73...@typhoon.nyroc.rr.com...
Tushar Mehta
separate out the various issues that seem to have become intertwined.
(1) If 1-2-3, or any other program for that matter, plots log(0), I
would be seriously worried about using that program for anything of any
importance. I'm curious. What does it do is one were to divide 1 by
zero?
(2) If you are annoyed with 'excessive' warnings about plotting
inappropriate data, I'm afraid you'll have to live with it. Plotting a
number <= 0 on a log scale is an impossibility! It might be nice for XL
to have a 'one time only' warning but, unfortunately, it doesn't. ;-)
Further, if it did implement a one-time-only warning, someone else would
complain about not being warned each time. If MS implemented that
choice as an option, someone would want the option on a global basis,
someone else on a workbook basis, or on a sheet basis, or on a chart
basis. And, if MS did manage to satisfy all those people, someone else
(probably me) would complain about bloatware <g>
(3) One could try and *avoid* plotting unplottable data. For example,
consider a plot on a log scale of the foll. data starting from A1:
01-Jan -1
02-Jan 0
03-Jan 1
04-Jan 2
05-Jan -1
06-Jan 100
07-Jan 1000
There are two ways to do this. First, in C1 enter the formula =IF(B1<=
0,NA(),B1). Copy this down to cover all rows with data. Now, plot
columns A and C.
The second option is to use a named formula. For my test, I used
PlotVals =IF(Sheet1!$B$1:$B$7<=0,NA(),Sheet1!$B$1:$B$7)
Now, plot column A and the named formula PlotVals.
(4) If you have data that are missing, XL handles that in one of two
ways. If a cell is truly empty (not just a zero length string such as
""), select the chart, then Tools | Options... | Chart tab. Select how
XL should handle missing points.
The second option is to simply put NA() in the cells you don't want XL
to plot.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <uw#$Kt9XBHA.2100@tkmsftngp03>, Micky Bitsco
<Micky...@hotmail.com> wrote
Micky Bitsco
> I'm not sure what exactly you are trying to do, but I'll try and
> separate out the various issues that seem to have become intertwined.
It's real simple Tushar, many engineering and financial programs plot date vs
data. Been doing it for at least 100 years. If you set Excel up to use date, as
in month, and have a month where there is no data, Excel insists the data has to
be 0 and will drive you crazy with error messages about 0 values. It used to be
Excel was smart enough to realize that if you had a null cell or a "" in the
cell, to not assume that the data is 0, because it isn't, you just don't have
any data for that day or month. Like your assumption, it is a simplistic
assumption on excel's part to assume on a date scale that you have to have a
data point for every time increment excel plots up.
> (1) If 1-2-3, or any other program for that matter, plots log(0), I
> would be seriously worried about using that program for anything of any
> importance. I'm curious. What does it do is one were to divide 1 by
> zero?
Read the above Tushar. You are not thinking of using a date vs data scenario.
There were work arounds for older versions of excel and lotus. It would seem MS
has gone off the deep end with thier assumptions about log scales in Excel 2000.
> (2) If you are annoyed with 'excessive' warnings about plotting
> inappropriate data, I'm afraid you'll have to live with it. Plotting a
No I won't. I'll just use Brand X spread sheet and/or write my own until MS gets
thier heads out of thier ass and gives the spreadsheet designer/user the ability
once again to not insiste that every time increment setup on the axis of a
semi-log chart -has- to have data associated with it.
> number <= 0 on a log scale is an impossibility! It might be nice for XL
> to have a 'one time only' warning but, unfortunately, it doesn't. ;-)
That right. With Excel 97, if the data corresponding to a date was "" Excel
Chart could be made to ignore it and moved on to the next time increment, which
is the correct thing to do. Like Excel programmers, you are steeped into
thinking that just because the x axis has a month or day, there has to be a data
point. Not true at all and never has been, not since the semi-log plot was first
used to plot time vs data.
> Further, if it did implement a one-time-only warning, someone else would
> complain about not being warned each time. If MS implemented that
I would guess that as overzealous as Excel is about these warnings, some Idiot
must of got a lawyer and sued them. Excel 97 could be coaxed out of the warning
if you used "" in the cell where there was no data. They "fixed" that with Excel
2000, which has forced me, like many many other users of Excel to find a
solution somewhere else.
> choice as an option, someone would want the option on a global basis,
> someone else on a workbook basis, or on a sheet basis, or on a chart
> basis. And, if MS did manage to satisfy all those people, someone else
> (probably me) would complain about bloatware <g>
It's already bloatware, and getting worse everyday.
> (3) One could try and *avoid* plotting unplottable data. For example,
> consider a plot on a log scale of the foll. data starting from A1:
A semi-log plot vs. date absolutely is -not- unplottable data. Never has been,
never will be. It just can't be plotted up using Excel 2000 without a lot of
annoying meaningless error messages. It actually plots up just fine in Excel,
after you whap the space bar or click Ok 43 gazillion times.
> 01-Jan -1
> 02-Jan 0
> 03-Jan 1
> 04-Jan 2
> 05-Jan -1
> 06-Jan 100
> 07-Jan 1000
> There are two ways to do this. First, in C1 enter the formula =IF(B1<=
> 0,NA(),B1). Copy this down to cover all rows with data. Now, plot
> columns A and C.
All dates are legitimate dates.
> The second option is to use a named formula. For my test, I used
> PlotVals =IF(Sheet1!$B$1:$B$7<=0,NA(),Sheet1!$B$1:$B$7)
> Now, plot column A and the named formula PlotVals.
Typically with these type of semi-log plots there are 4 or more sets of data to
plot up. Some sets may not have data in a specfic time, but others will.
> (4) If you have data that are missing, XL handles that in one of two
> ways. If a cell is truly empty (not just a zero length string such as
> ""), select the chart, then Tools | Options... | Chart tab. Select how
> XL should handle missing points.
Don't that, over and over, that doesn't work with semi-log plots.
> The second option is to simply put NA() in the cells you don't want XL
> to plot.
Done that. Then Excel gets really screwed up in the way that it draws lines
between points.
Tell you what tusha, you make a semi-log plot of 4 sets of data vs month or day.
Have one set of data be in the range of 10, one in the range of 500, one in the
range of 5000 and the last in the range of 1-5. Put the numbers on the log
scale. Have some days or months that there are no sales, incidents, etc of one
of the sets, and have data with the other 3 as is -quite- common in the real
world. Go through all this advice you are giving me and see if you can get it to
give you a decent presentible plot before you start telling me all about
semi-log plots in Excel. If you have not tried it you really don't know what it
is I and many others in this conference are talking about.
But thanks for trying to help us anyway.
Tushar Mehta
show her/him how to do the what 'can't be done.' In fact, a bunch of
regulars in these NGs could do that in their sleep.
Clearly you did not try or test any of my suggestions.
It is incredibly arrogant for you to
assume that I didn't test or I don't use what I suggested.
talk about 'real life,' as though you have a monopoly on it.
be so condescending without knowing what I know about engineering,
finance, or XL.
It is perfectly OK to bitch about XL and MS. I don't mind, and might
even join you every so often. It is doubtful that MS minds. In fact,
if no one did complain, MS would think it wasn't effectively exploiting
its dominant market power. Just make the rant clear in your post so
that I -- and other volunteers -- don't mistake it for a serious request
for help.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <uhpnKJIYBHA.1908@tkmsftngp07>, Micky Bitsco
Micky Bitsco
> You know what? Point your boss or client to this NG. I'll be happy to
> show her/him how to do the what 'can't be done.' In fact, a bunch of
> regulars in these NGs could do that in their sleep.
None of the suggestions you made work Tushar. It was you who questioned the
validity of having a semilog plot that had no data in it for a particular time
period. I have tried every one of your suggestions and many others. Set up 4
sets of data. Do it with date, by month on the x axis. Try a simple and very
common one. Production of 4 differernt types of widgets vs month. You'll produce
about 10,000 A widgests, 200 B widgets, 5000 C widgets and 1200 D widgets. Some
months you'll not produce any A widgets becaause you have to produce more B
widgets. There is no data for the A widget say in the month of March. Ove a
period of 5 years each and every type of widge is not produced in 4 of thosse 60
months. You need a semi log plot comparing output of all 4 types of widges.
Where there is no data for a widget for a month, please leave a gap in the plot.
Make a graph of it and report back would you tushar?
> Clearly you did not try or test any of my suggestions.
Tested everyone of them Tushar and some that you are not aware of..
> It is incredibly arrogant for you to
I am not the one being arrogant here. Please make the above graph and tell me
who is being arrogant. All I want is a solution to my problem with Excel 2000.
All we want to do is turn off the error message.
Tushar Mehta
> validity of having a semilog plot that had no data in it for a particular time
Wrong. I questioned wanting to plot log(0). Here's what I wrote:
> > > > (1) If 1-2-3, or any other program for that matter, plots log(0), I
> > > > would be seriously worried about using that program for anything of any
> Make a graph of it and report back would you tushar?
When I post an untested suggestion, I *clearly* indicate that.
Like I said, point your boss or client to this NG, and chances are
he/she will leave happy.
> All we want to do is turn off the error message.
As we discussed, it might (and I'm still not sold on the idea) be nice
to have more options. However, it doesn't change the underlying problem
with the data set. XL doesn't generate an error message when a cell is
empty. It generates the message when one tries to plot log of a value
that is <= 0. There is *no* real representation for that value. It's a
mathematical impossibility.
This discussion, as it is, is going nowhere. You're convinced XL can't
create a graph with missing data. I know it can. It is highly unlikely
we can bridge the gap.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--
In <O#8qQJRYBHA.1048@tkmsftngp05>, Micky Bitsco
Micky Bitsco
plot up a date versis semilog with more several varibles. So please, do not
respond to this message any more. Like you, I am tired of the thread you and I
seem to have got our selves into. Over the last several months that I have
followed this conference, the same question that I entered has been asked. So
far as I can tell no one has had a satisfactory solution to their problem from
this conference. Perhaps some one besides has found a viable workaround to
plotting several varibles vs. date on semi-log plot.
Don't respond Tushar, let's see if some one else understands the problem and can
respond intelligently to the topic.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.1649be284...@msnews.microsoft.com...
Michael Edison
Please see the attached file (privately sent) and let me know why this does
not meet your needs. For the first data set, I filled in the missing data
with #N/A's. As you have already mentioned, Excel will connect the points
on either side of these missing values. I do not see why this is
necessarily a bad thing, adding data markers to the lines makes it fairly
obvious that there is a missing data point. However, you would like to see
the gaps in the chart. Look at the second set of data. Look familiar? It
should. But, something's missing? What could it be? Those #N/A's!
They're gone! Look at the 2nd chart. Those data points! They're gone too!
How could those #N/A's disappear? Somebody erased them!?! If only there
were a way to quickly erase those #N/A's. Hmmm. Erase a cell's contents?
Is it possible? I've never done that before. Obviously, neither have you
since, "I have tried every one of your suggestions and many others." I
wonder if anyone has done this before?
(Five minutes later)
We're in luck! I just got off the phone with a bona fide computer genius
and apparently, get this, there is a button on the keyboard that, when used
in Excel, will erase the contents of the selected cells!!! She said it was
called the deleet (deleat?) key. Wait a second? I can't find it? Where's
the deleet key? Hmmm. Well, I have had this computer for a couple years
now, it could be outdated. I bet the deleet key is a new feature!
(a couple hours later)
I don't know what to tell you! I've been to dozens of stores and looked at
who knows how many keyboards and not one of them has a deleet key. Until we
find this deleet key, I don't think we'll be able to make those charts.
You suggested that you might develop your own spreadsheet. I've got a
better idea for you. How about creating a keyboard with the deleet key on
it! Think about it. It would be a sure thing; all you would have to do is
demonstrate how powerful the deleet key can be (these charts should
suffice). Or, better yet, you could get together with the guys at Microsoft
and sell all future releases of Excel in a package with your new keyboard!
********
The original poster to this tread wanted to know how to get rid of the
message that appear when negative values were entered in a log-scaled
chart's data range.
The original poster also stated, "I can fudge the data to remove the
negative result, but I would rather just get rid of the message."
Tushar responded, "While it would be nice to suppress the warning on
occasion, most of the time I would rather see the warning when I'm plotting
inappropriate values than plot illogical values and not be warned ;-)"
It sounds like the original poster already had a way to plot log-scaled
charts with, believe it or not, missing data points!
But in your last post you claim, "So far as I can tell no one has had a
satisfactory solution to their problem from this conference. Perhaps some
one besides has found a viable workaround to plotting several varibles vs.
date on semi-log plot."
Further inspection of the other posts reveals that the original poster
SPECIFICALLY MENTIONS the solution to your problem, right before you jumped
into the discussion, "I would like to write a blank cell into Excel when the
data in invalid so that it overwrites old data, but I can't get it to poke
in a blank cell."
I realize that the original poster had a typo in this sentence (must not
have a deleet key either), but you should still be able to understand it,
shouldn't you?
The original poster provided a couple examples about how he replaces the
invalid data. Surprisingly, he mentions using both #N/A's and blank cells.
Which, when charted, provide you with a chart with or without gaps depending
on your preference (I think we all know what your preference is by now).
In a later post in response to you, Tushar provided a couple methods for you
to replace the invalid data with the #N/A's, one of them being:
=IF(B1<=0,NA(),B1)
Unfortunately for all of us, he did not provide the answer that you
required. More specifically, Tushar did not describe to you the magic of
the deleet key.
Now, when I originally started this reply, I was not going to fulfill your
last request and "respond intelligently." I think I got most of the sarcasm
out of my system halfway through this post. I realized that you are not the
only person that reads through these newsgroups, they exist so that people
can help each other out, answer each other's question and share experiences
with one another.
So, I am going to try to help you out. I am going to provide some advice
that will help you unleash the power of the deleet key and create your
chart!
Use Tushar's formula to create a data table consisting of the valid data and
the invalid data replaced with #N/A's. (be careful, Tushar's formula
reference's cell B1, it's possible that you will need to adjust this
reference to match your data!)
Edit - Copy this new data table
Edit - Paste Special - Values (this will get rid of the formulas)
With just the data table selected: Edit - Go To. . .
Click Special
Click Constants
Uncheck all of the boxes except for Errors
Click OK
All of the #N/A's should now be selected
Press the magic key (the deleet key if you forgot)
Blank cells have replaced all of the invalid data!
my two censt, (if only I had a deleet key!)
Mike
"Micky Bitsco" <Micky...@hotmail.com> wrote in message
news:uvyE7.1$Y4....@news1.trib.com...
Micky Bitsco
have done exactly what you showed me. That works in both Excel 2000 and in
Excel 97.
My situation is a bit more complex and is a dynamic situation. Perhaps it is
a bit too complex to be trying to plot up with Excel anyway. I capture
several columns of production data off the internet, and process it into a
date vs. rate semi-log plot. There is no problem in capturing and plotting
up the existing data, as required leaving "gaps" in the months there is no
data. The user then generates a curve fit for each data set to project
future performance, using, as his human judgment requires, both linear and
hyperbolic curve generation equations. It's quite handy to have the auto
calc on while the user is typing in the 10 different variable parameters
(Start date, hyperbolic decline rate, exponential decline rate, years in
hyperbolic curves, producing limits, etc). The projection can't be
performed with two baboons and a computer. A knowledgeable human being has
to be sitting at the keys to manually fit the curve and make the performance
forecast. For each value they put in, they have to immediately see it
plotted up. That seems to be where the problem lies in Excel 2000 as
compared to older versions of Excel and Lotus when doing semi-log plots.
Sorry to not of got back to you sooner about your very kind response.
Although ugly right now, I can over come the above problem by compiling the
program in Visual Basic. What seems so maddening to me in this whole thing
is that I can't just shut the damned meaningless error message off allowing
the human who is running the program make the judgment as to the quality and
accuracy of the data. That seems like such a practical simple solution that
I know I and many excel users who want to make some what more complex
semi-log plots and extrapolation projected on semilog co-ordinates would
greatly appreciate Microsoft putting some kind of simple "fix" or override
back into it.
You comments have helped. If I get time, I will make up a simplified non
company owned spreadsheet of what we need to do and send it to you. I really
would rather do it in Excel than Visual Basic as it's going take me quite a
bit of time to make it more user friendly and produce as pretty of semi-log
plots as I was able to do in Excel 97.
"Michael Edison" <medi...@hotmail.com> wrote in message
news:vELE7.93339$kf1.30...@news1.rdc1.ne.home.com...