+ Reply to Thread
Results 1 to 12 of 12

Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log charts

  1. #1
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log charts

    Hi All,

    How to disable this error message when use Logarithm Scale in excel chart. I have also tried replacing formula with =NA(), and "#N/A" as text but both are still not working. This stupid bug has wasted my time.

    I'm highly appreciate if anyone could help me.
    1. Thank those who have helped you by clicking the Star * below the post.
    2. Please mark your post [SOLVED] if it has been answered satisfactorily.

    Sincerely,
    Farid

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    What makes you think it's a bug? Doesn't sound like one to me.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    Hi,

    Wrap the formula that's producing the Log value in an =IFERROR() function.

    Self evidently since the log of a number is the power to which 10 must be raised to equal that number, you can't have a negative log.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    Hi AliGW/Richard Buttrey

    Please see sample of attachment and click on any buttons on slicer.
    if value = 0, I have changed to =NA() or "#N/A" but still unable stop the error message.
    Attached Files Attached Files

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    Try changing the charts source data so that it uses the range with #N/A values rather than the one with zeros, which will cause the warning message
    Cheers
    Andy
    www.andypope.info

  6. #6
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    I have done it but the result still same.

    Source Data : PivotTable Options > For Empty Cells Show : #N/A

    I have also search the same topic in another forum but the answer are same with my approach use =NA() or "#N/A". This error is bug should be removed by Microsoft
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    This error is bug should be removed by Microsoft
    It's NOT a bug - it's a limitation of the chart you've chosen to use - it says clearly in the error message given what the problem is.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    I'm inclined to agree in part with faridwahidi. It may technically not be a bug (because it's working as intended), but there must be a less intrusive way to communicate the error. Maybe A little green triangle (like for in cell errors maybe controlled by the same error checking options) or something.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    Hi Mr Shorty,

    So, there is no way to remove this error message?

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    I haven't found a way.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    See example file.

    I have added IFERROR formula around your formula to output #N/A.
    Also added a line series to display data labels, as column chart will display #N/A

    Also changed the show empty cells as
    So the extra chart is now directly linked to middle pivot table. You can change to Log axis with the error warning.


    I assume the logic is that the warning is a real crucial one mathematically. If you include an option to suppress the warning it would get switched off and never been seen again.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Logarithm Scale, Disable Negative or zero values cannot be plotted correctly on log ch

    Hi Andy,


    Even though not really solve the issue but at least I learned something. Thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 03-28-2012, 11:32 AM
  2. Controling how zero values are plotted in charts
    By crawshawpaul in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-21-2006, 04:40 PM
  3. How to correctly scale X-Axis in Charts?
    By Peter Bernadyne in forum Excel General
    Replies: 3
    Last Post: 12-20-2005, 05:47 PM
  4. [SOLVED] Disable warning for negative numbers on log scale plots
    By Browning6 in forum Excel General
    Replies: 0
    Last Post: 11-13-2005, 03:45 PM
  5. Logarithm equation in charts
    By Thiem in forum Excel General
    Replies: 2
    Last Post: 09-22-2005, 02:05 PM
  6. Excel Charts - Data being Plotted beyond Primary X-Axis Scale
    By Yvonne Fochesato in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-30-2005, 11:06 PM
  7. [SOLVED] Logarithm scaling for charts
    By vroomvondel in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-15-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1