Negative numbers are not displaying in a clustered bar chart

Question

Hi, 

I have a spreadsheet with the periodic table elements with their melting and boiling points.

I have two problems.

Firstly, even though I select the whole 92 elements in the three columns, only some elements are displaying in the chart. It always ignores the first 2 negative numbers as well as the 92nd element with both positive values. But there are elements with positive values missing as well. have tried multiple times with the same result. Expanding the chart sideways makes no difference. They simply aren't there.

Secondly, am trying to display it in a clustered bar chart, but can not work out how to display negative numbers below the x-axis.  

Cells are formatted as General. I tried number for the boiling points, but the result was worse.

Any suggestions greatly appreciated.

Donna

Answer 1

I have added it to my personal OneDrive from this link: https://1drv.ms/u/s!AgTCHHd63TVigg_ZhUsEiOes-wpR?e=LSoMcJ

Hi Donna,

the issue is that most of your numbers are not numbers but text. Select column C:D and remove the alignment:

Anything that is aligned left is a text. The minus "-" is a "–", replace it manually or execute this macro once to convert the text to numbers.

Sub Test()
  Dim R As Range
  Dim V
  For Each R In Range("C2:D93")
    V = R.Value
    If Not IsNumeric(V) Then
      V = Val(Replace(V, ChrW(8211), "-"))
    End If
    R.Value = CDbl(V)
  Next
End Sub

Delete the chart, select column A,C:D and create a new chart, now you can see all elements and bars.

The horizontal axis labels shows only every 2nd element because the interval is set to automatic, format the axis and set the label interval to 1.

Andreas.

Answer 2

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you. So we need to see your file.

IMPORTANT: Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Answer 3

Hi Andreas,

I am afraid I can’t share the OneDrive link using the profile I used to log the request in Microsoft support, as the email address is my work one. If I try and share the file/folder with anyone outside of my work organisation, it will fail.

I have added it to my personal OneDrive from this link: https://1drv.ms/u/s!AgTCHHd63TVigg_ZhUsEiOes-wpR?e=LSoMcJ

Kind regards,

Donna

Answer 4

Excellent thank  you Andreas. It wasn't a spreadsheet I had created myself, so didn't pick up the hyphen vs minus sign. I have followed all of your directions and it is perfect now :-)