[Solved] Create chart with missing entries on X-

Discuss the spreadsheet application

[Solved] Create chart with missing entries on X-

Postby lafeber » Mon Jan 22, 2018 11:58 pm

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
lafeber
 
Posts: 2
Joined: Mon Jan 22, 2018 8:10 pm

Re: Create chart with missing entries on X-axis

Postby MrProgrammer » Tue Jan 23, 2018 7:02 am

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 4 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   Expand viewCollapse view
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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3200
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby lafeber » Tue Jan 23, 2018 12:25 pm

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   Expand viewCollapse view
=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.
https://forum.openoffice.org/en/forum/v ... =9&t=35802
Thanks.
OpenOffice 4 on Windows 7 Pro
lafeber
 
Posts: 2
Joined: Mon Jan 22, 2018 8:10 pm

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

Postby jrkrideau » Tue Jan 23, 2018 2:56 pm

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 5 times
OpenOffice 4.1.4 Ubuntu 16.04 Xenial Xerus
jrkrideau
Volunteer
 
Posts: 3414
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: Teevo42 and 42 guests