Turn off Error Alert when using zero values in a Logarithmic Scale

  • I'm creating several dozen charts all using Logarithmic Scale and while I know you aren't supposed to provide Zero values in the source data this is one project where that can't be helped. My problem is that the Excel Alert keeps popping up to tell me I shouldn't use zeros and it keeps crashing my sheet because it pops up every 10 seconds or so.


    My question is: Is there any way to turn off an Excel Error Alert? OR Is there any value I can place in those cells (other than Zero or One) that would stop that troublesome alert from popping up?


    Just for reference, this is the alert:
    MICROSOFT EXCEL
    Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following:
    * Enter only positive values (greater than zero) int he cells used to create the chart.
    * In the chart, click the axis you want to change. On the Format menu, click Selected Axis. Click the Scale tab, and then clear the Logarithmic Scale check box.

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    If you use Vba, perhaps this helps:


    Code
    Application.DisplayAlerts = False
  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    Thank you for your suggestion! I'm a novice at VBA. I understand how to record and do some simple modifications and how to assign a macro to a clickevent etc, but how do I make the Application.DisplayAlerts run all the time?

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    I think it's only active during the macro. This could be a problem I assume.

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    I don't believe you can turn this warning off.


    Replace the zero values with #N/A. Either as text or as the result of the NA() function.

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    Tried the #N/A method, doesn't work. Tried just entering a space as a text string, no good. There HAS to be a way around this stupid Alert. It should stop popping up after the first warning but it does it every time the chart comes on screen, on every click event, even every time I type in a cell where the Chart is within view.


    I wonder if Grouping to change the chart to an Image rather than active content would help...

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    I think =NA() worked! I'm still testing, but it looks like that was the magic bean I needed! THANK YOU! YOU ARE A GENIUS!

  • Re: Turn off Error Alert when using zero values in a Logarithmic Scale


    I had the same problem but it did not exist when I opened the same file in the FREE Open Office Calc. That's what we get for making Bill Gates rich!!!
    Visit www.openoffice.org/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!