[Solved] Create chart with missing entries on X-

Discuss the spreadsheet application
Post Reply
lafeber
Posts: 2
Joined: Mon Jan 22, 2018 8:10 pm

[Solved] Create chart with missing entries on X-

Post by lafeber »

Hello all,
I am trying to create a bar chart with numbers plotted on the Y-axis as a function of the number of countries found on a certain index. Because of the spread, not all index numbers appear. That means that 0 countries are found on those index numbers.
When I create a graph over the table that I have, only the index numbers where a value is set (1 or more) appear. How can I tell the application to plot from 0 to 100 and draw no bar on those index numbers?
In small, on a range from 1 to 10, this could have been the table:

Index | Count
2 | 3
3 | 1
7 | 2
9 | 2

I would like to have an X-axis going from 0 to 10 and show bars of height 0,0,2,1,0,0,0,2,0,2,0.

Can I get Calc to draw this for me?
Note; This is my first post. I found a button "Table=" but couldn't findn an explanation in the sticky posts or via Google to tell me how to use this. If someone can help me, I will make sure to use it next time.
Last edited by lafeber on Tue Jan 23, 2018 12:25 pm, edited 2 times in total.
OpenOffice 4 on Windows 7 Pro
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Create chart with missing entries on X-axis

Post by MrProgrammer »

Hi, and welcome to the forum.
lafeber wrote: Because of the spread, not all index numbers appear. That means that 0 countries are found on those index numbers.
Use chart type XY Scatter. Then Insert → Y Error Bars → Percentage → Negative → 100% → OK. Format X-Axis as desired, 0 to 10 in this case. Format Error Bars as desired (width, color).
201801222248.ods
(12.91 KiB) Downloaded 72 times
lafeber wrote:I found a button "Table=" but couldn't findn an explanation in the sticky posts or via Google to tell me how to use this.
No one is successful with the Table= or Aligntable= buttons. Use the Code button to describe your table with a monospace font. Better yet, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). It's easier for you and for us.

Code: Select all

Index  Count
  2      3
  3      1
  7      2
  9      2
lafeber wrote:I would like to have an X-axis going from 0 to 10 and show bars of height 0,0,2,1,0,0,0,2,0,2,0.
Based on your sample data you will get 0,0,3,1,0,0,0,2,0,2,0.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
lafeber
Posts: 2
Joined: Mon Jan 22, 2018 8:10 pm

Re: Create chart with missing entries on X-axis [SOLVED]

Post by lafeber »

Thanks. The "scatter chart" was indeed the solution (and term) aI was looking for. I would still have preferred to have the data represented in a bar chart, but this will do.
In the meantime, I applied a workaround; I created a table containing values 0-100, and filled it with a formula using VLOOKUP to pump over the values and fill the rest with zeroes;

Code: Select all

=IF(ISNA(VLOOKUP(D2;$A$3:$B$57;2;0));0;VLOOKUP(D2;$A$3:$B$57;2;2))
BTW, I finally found the answer about tabes. I didn;t know about BBcode. For me, it's simply replacing triangular brackets <> with angular brackets [].
I found a post with the same question, but sadly there is no way to make table tags work. So indeed code is the best alternative.
viewtopic.php?f=9&t=35802
Thanks.
OpenOffice 4 on Windows 7 Pro
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Create chart with missing entries on X-

Post by jrkrideau »

You can do the chart as a bar but you need two vectors
y-axis = (0,0,2,1,0,0,0,2,0,2,0)
x-axis = ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

See attached file.

How that works with the rest of what you are doing I don't know. I assume it would mean a lot of hand editing

This post is a replacement for an earlier and incorrect post that I managed to delete when I intended to edit it. :knock:
Attachments
labbar.ods
(12.96 KiB) Downloaded 71 times
LibreOffice 7.3.7. 2; Ubuntu 22.04
Post Reply