Naked Science Forum
Non Life Sciences => Geek Speak => Topic started by: imatfaal on 30/06/2011 23:44:22
-
Anyone got a hint for a charting ap or add-on to excel? I would like to be able to create a chart that works like, but not the same as a histogram - ie the width of each block corresponds to the duration of measurement and the height corresponds to the rate for that particular period and thus the area corresponds to the number of instances during the period.
I would love to know if there is an excel addon or a nice cludge for this. I could do it with a graphics program but I would like to be able to generate the chart quickly. Any ideas?
Thanks Matthew
-
I assume you are looking for something like this:
http://www.slideshare.net/FordMotorCompany/diesel-forum-presentation
[ Invalid Attachment ]
Although, this chart is actually only defining X (Particulates) and Z (NOx) with Y being the year.
I see this chart has different definitions of KW and HP. Anyway, it seems to be "Odd" to make.
I think I can fudge a similar chart using OpenOffice Version 3.3, but it certainly doesn't seem to be very clean. More notes in a few minutes.
-
Ok,
I just threw this together using OpenCalc (OpenOffice 3.3) which is a free utility. I use Linux, but it is also available for WinDoze.
[ Invalid Attachment ]
Chart Type was: Area - 3D - Realistic.
Data series in Rows
First Row as Label
First Column as Label
I had to create a separate column for every possible value on the X axis which was a pain. I suppose I could do the same thing if I wanted to fatten the Y dimension, by adding more rows and messing with the colors, but it would also mess up the 3-D shadows.
Excel XP would do a similar chart.
The one tricky thing with OpenChart was that I had to click on a "data set" and change "Plot Missing Values" from "Assume Zero" to "Continue Line". What that gave me was a nice square end (otherwise it would slope).
I assume Excel has that option, but I couldn't find it.
-
That's fantastic Clifford - thanks for the effort. That actually solves one of other problems perfectly.
For the original question I will be more specific. I would like to portray the earnings of the ships I commercially manage in an easily accessible way. If, on a 2d graph, I have the date on the x-axis then the width of each coloured block will provide an indication of the voyage length in days. Then on the y-axis I will have dollars per day earnings - thus thus height of each block will indicate the daily return for each voyage. And of course the area of each block will indicate the net freight for each voyage.
The 3-d blocks stacked from front to back in your great examples would not work - because I want the blocks stacked chronologically, and the last voyage could be the shortest and at the moment will definitely be the worst performance: ie it will be hidden!
Again thanks your efforts - will check out open-office because of this!
-
Ok,
I haven't seen a note back to see if this is what you are looking for. It is seriously hacked... to get these 3-D bars [xx(]
(having to create a column for each possible length of bar).
But,it does seem to work.
With Excel XP, I couldn't find the option "Continue Line". I was able to get the vertical lines on the end by going to "Source Data", and setting the data range to only use those valid cells, and excluding the empty cells.
In XP, (and similarly in OpenOffice), by right clicking on each individual data series, and selecting format data series, I can set border to "none", and thus get rid of the lines across the top of the blocks.
Oh.
Looking back at your original request.
Were you wanting to do this in 2-D rather than 3-D?
I could do the same thing in 2-D. However, I had to set all my bars the same color, and it looked much more funky because the bar spacing is non-uniform.
[ Invalid Attachment ]
-
Ok,
As far as Excel vs OpenOffice. I think they are similar. They can do more or less the same things, but do it slightly differently. I actually find Excel is slightly more intuitive, and slightly smoother. But, I use OpenOffice because it is FREE, and I am trying to support Open Source, and dislike monopolies.
I tried it again.
[ Invalid Attachment ]
This time I ended up with hollow bars. Sorry, couldn't realistically get filled in bars.
It was a bit of a pain to set up. But, it is much more stable this way. And, if you don't add more bars too frequently, you could easily set up formulas to automatically fill the cells. In the above graphs, the width of the bars was specified by the number of cells filled.
In this case.
Chart Type: XY Scatter. I always choose connected lines, no markers.
Then I created 4 separate lines (blue, orange, yellow, green).
For the first bar
Duration 4
X values are C2 to C5
Y values are D2 to D5 (I now have it set so blanks are treated as zero, you may need to add zeros to cells D2 & D5).
For the second bar.
X values are C6 to C9 by adding 1 to first bar, then incrementing, in this case duration = 1.
Y values are D6 to D9
For the 3rd and 4th bars, I chose to do non integral widths (which I would not have been able to do with the previous methods).
My biggest problem is that I can never quite get my X-Axis labels to come out like I would like them.
Sometimes I just give up, and create a text box to hand-write my own X Axis.
Anyway, if you don't mind hollow boxes, and the cumbersome X Axis, this is very clean to write.
-
Thats amazing - thanks all your work. Will try it out Matthew