+ Reply to Thread
Results 1 to 14 of 14

Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter.

  1. #1
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Angry Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter.

    As the title shows, that is the problem I've been having with Excel and I can't figure out what the problem is and it's driving me mad.

    I am trying to make a scatterplot with values on the Y-axis having three decimal numbers, but whenever I change the decimal numbers to anything that isn't 2 it changes everything to a bunch of zeroes. (See attached image).

    The values in "Average" are all numbers.
    The values haven't been copy/pasted so it probably doesn't think it's text.

    Screenshot 2018-10-13 at 11.58.58.png
    Attached Files Attached Files
    Last edited by po tato; 10-13-2018 at 06:17 AM. Reason: Adding attachment

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    Welcome to the forum!

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    Thanks I added it now.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    The number columns are set as general - however, they respond correctly if the cell format is changed to number.

    I do not understand or see what this issue is:

    Whenever I change the decimal numbers to anything that isn't 2 it changes everything to a bunch of zeroes.
    Where is this "bunch of zeroes"?

  5. #5
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    It's in the screenshot that's also attached.
    The data is has the correct amount of decimal numbers, three.

    But the Y-axis on the left graph below shows 0,000 instead.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    I'm really sorry, but I do not see that here. The number of decimal places increase in the veritcal axis labels, but the values do not change to zero.

  7. #7
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    Ugh, thanks for taking the time to help.
    Guess it's a problem with my version only then.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    I am running Excel 365, but with a UK locale.

  9. #9
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    I mistakenly put 365 in my profile but it's 2016

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    OK, well hopefully someone with the non-subscription version will chip in. Please update your profile to correctly reflect the version you are using. Thanks.

  11. #11
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    I updated it, thanks again for your time

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

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    If it helps, when I open your sample file, everything looks normal. Naturally, my copy of Excel uses "." for the decimal separator, so that's what I see.

    Related to that, one thing I notice in your screenshot is the inconsistent use of "," as the decimal separator. I notice that your spreadsheet cells are all using "," as the decimal separator. However, 3 of the 4 axes (the 3 axes that look correct) appear to all use "." as the decimal separator. The axis that is having the problem appears to be trying to use "," as the decimal separator. I have never seen Excel use a mix of decimal separators (one part of a spreadsheet uses "." and "," as decimal and thousands separators, while another part of the same spreadsheet uses "," and "." instead), but that almost looks like what is happening. And that could make sense. If the three correct axes are formatted to show tenths, and the non-working axis is somehow formatted to show thousands (where the largest value is less than one-half), then the display would be just like your screenshot.

    I would suggest you look real carefully at what Excel is trying to use for decimal and thousand separator and make sure that it is doing this correctly. As one who never really works with anything other than the US standard of "." and "," for decimal and thousand separator, I cannot comment on exactly what to look for to correct this kind of thing, but that's where I would be looking at this point.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  13. #13
    Registered User
    Join Date
    10-13-2018
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    Amazing observation MrShorty!
    I looked around a bit and I found this:
    Example Before.png
    As you can see the Format code in the bottom right is as I want it to be and it puts it in the category "Custom".
    I can however change the , into a . in Format Code and this happens:
    Example After.png

    It seems to be a bug of some kind because it doesn't seem to be possible to keep it as a , and make it work.
    I'll use the . instead for now, thanks a lot!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,561

    Re: Vertical axis shows 0s when more or less than 2 decimal numbers are present in scatter

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Bar Charts go away when I change Vertical axis numbers.
    By General Fear in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-31-2017, 08:41 AM
  2. Scatter Chart Without a Vertical / Y Axis
    By emcichon in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-11-2017, 02:12 AM
  3. Replace numbers on x-axis of scatter chart with text
    By Blake 7 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-12-2015, 08:39 AM
  4. Wrong numbers on Y-axis in scatter diagram
    By pottesur in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-12-2015, 03:30 PM
  5. Add labels/titles to vertical axis of scatter chart
    By verwater in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-23-2013, 07:19 PM
  6. Change Numbers to vertical axis
    By amartino44 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-31-2013, 01:47 PM
  7. Replies: 1
    Last Post: 09-10-2009, 04:01 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