The Chart Missing From ALL Spreadsheet Software
ฝัง
- เผยแพร่เมื่อ 23 พ.ย. 2024
- Support MinutePhysics on Patreon! / minutephysics or Gift a Patreon membership
: / gift
I was supposed to thank these patrons on-screen:
Pierre Lanson
David Adams
Xierumeng
Joseph Welford
Jason Anaya
Amnon Levi
That Asymptote
James Kyouhei
Scott Crawford
Andreas Chris Wilhelmer
Patreotic Pleb
Jean-Luc Cooke
Emanuel Kahler
rotem
Andy Hsu
Joe McCann
Ceolifer
Michael Millar
Jesse Simmons
Matt Darcy
Tabelel
Manish Kumar
Myles Kornfeld
John J White
Long Quan
Fergus Simpson
Diane Lehmann
Miranda P.
Lee Denison
barelylingual
M K
franspaco
frowniebrownie
Forged by Geeks
Cody Powell
J. Tower
Leo Wang
Tim
Nodraak
Joe
Max DeStefano
Kade Murray
Condor103
donbataco
NuSigma
Iced Lava
Rafael
Alex Martin
Ben Solomons
Eric Pan
Matthew Norman
Pierre Avital
Jason Hubbell
Nick Kruse
Jo Louie
John VanderPol
A
Jacob Grover
Water
Sean Holloway
Andrea
Seth
Lara Benham
Benjamin Loftus
Waldo
HiggsAnyon
Thomas Warfel
Terry
Hades
Kevin Mattick
Shardul Heda
Danyel Cavazos
Quinn Bright
SL Haanschoten
Zachary Condon
Jason Singh
Martin Kahnt
Stephen Cummings
CruxCapacitor
Thomas Levy
Tristan Bellman-Greenwood
Ethan Torres
Tom Schoeber
Clutz
Stefan Papastefanou
Cary Harper
Jordan Pitlor
Jaques de Bois
Patreon Sucks
Tyler Nelson
Stan
Sam Wilkinson
LivingInTheNow
Ian Tillman
Pierce Moore
Caitlin
Ethan Anderson
Patreon Supporter
Ubiquity Ventures
Lukas Biewald
Muhammad Shifaz
Jeff Straathof
Link to Patreon Supporters: www.minutephysi...
Minute Physics provides an energetic and entertaining view of old and new problems in physics -- all in a minute!
Created by Henry Reich
This is a bad day at the office turned into a video rant 😂
Instead of "this meeting could have been an email" we upgrade to "this meeting could have been a TH-cam video rant"
So true😂😂
Das war ein benfehi! lol
It is now a bad day at Google and Microsoft
2:05 "...add a really big number..."
And, given this choice, you seriously add 100 instead of 273.16?
Underrated comment.
This is exactly how i implement this issue in my college; and override the labels from K to C
Because then you've got the helper lines at really odd places
This!
@@Steamrick Or use Fahrenheit, the better scale, and then in most cases not need to deal with negative numbers at all. And in the rare cases that you do, you switch to Rankine and preserve meaningful helper lines.
The one and only time London Ontario will be mentioned outside of London Ontario
Not Just Bikes also mentions it regularly :)
Today I learned about London Kentucky
@@adrianburmester1159but it’s Fake London
@@adrianburmester1159 he's from that fake London so it doesn't count as being outside
I'm a hockey fan from Arizona, I talk about London Ontario from time to time
I love how you managed to make "London" into an axis
And an existential crisis! 😂
@@danielnarbettaxistential
Matplotlib stacked bar chart allows (actually requires) you to specify both the start and the size of each rectangle you draw, so you can do this kind of plot pretty easily.
True, but he probably meant without coding
Any programming tool let's you do this with ease, it is essensial in data analysis where you need to display certainty using error range.
@@crazedvidmaker I highly agree.
In the future every human should be required to know how to code. It's fundamental to humans today and the key of automation and technological advancement of the tomorrow.
Coding shall be teached with the same importance as Language.
Also, I'd like to add that applications like Excel are basically "programming in a trench coat" anyways. Simple pyplot/pylab-style programming isn't really all that more advanced compared to the GUI-based alternatives. Once you get used to configuring stuff by writing text instead of clicking on buttons, you should be able to learn the basics pretty quickly.
@@Splarkszterlaughable, I agree computer skills are important but we are far far from it attaining common sense status, if ever. You could apply the same logic and say everyone should know how to make a PCB or how to design circuit logic or any number of other things essential to how we live today
Long time watcher here. This actually exists in the HighCharts JavaScript graphing library as a "columnrange" type. To make it a "bar" just set inverted: false. There are wrapper/plugins for it for both R and Python.
why do we need to know that you've been watching these videos for a long time?
@@ReynaMirez He's trying to grab attention
I used to work in Excel a ton and made this chart all the time. You just added an extra category to create that offset and format it to be borderless and transparent.
This is what I do as well. Invisible first category with a common minimum, and deltas thereafter
The problem remains that it is a hacky workaround
@@christiannorf1680 Pretty much every time you want to put a professional level chart in Excel you end up with a hacky workaround. Speaking as someone who's made a career out of hacky Excel shenanigans 😉.
@@christiannorf1680 But most people don't have the intuition to even recognize its hackyness nor how it could bite them in the future.
Most software requires a lot of workarounds and so people naturally get used to that. It's still annoying, but if you don't have first-hand experience with or understanding of the causes, you just put up with the bugs instead of pushing to fix them.
What if you want to start with a negative number?
This is why ggplot is the superior plotting ecosystem. If I had to make this chart, I would have never even realized this was impossible in most plotting libraries. Through the 'grammar of graphics' ggplot provides this would have been quite simple to figure out.
I was thinking throughout the video "there must be a way using ggplot". I'm glad I use R and this package instead of excel.
I was thinking if that is not directly in ggplot, maybe there is a way using geom_segment
He included plotly in the thumbnail for some reason, plotly has an R/ggplot api...
There are a few ways to do so in R. The easiest option is HH::likert(). The other easy options use ggplot() via geom_crossbar(), geom_pointrange(), or geom_linerange(). Then it can be converted from ggplot2 to plotly using plotly::ggplotly(). I think there are other options via other geoms, but may not be as efficient.
Toy Example:
df %
ggplot() +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = record_max,
ymin = record_min,
),
fill = "darkred",
alpha = 0.1
) +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = mean_max,
ymin = mean_min,
),
fill = "darkred",
alpha = 0.3
) +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = daily_max,
ymin = daily_min,
),
fill = "darkred",
alpha = 0.5
) +
labs(
x = "x",
y = "y"
) +
theme_test()
Software engineer here. This kind of chart is incredibly easy to program in any advanced spreadsheet or plotting software. That is, the answer your question "is this too much to ask?" the is "absolutely not". Developers can add this chart quite easily using the existing charting engines.
True, but making this work in matplotlib is a pain
Yeah I was thinking that. I think I might just whip one up in Makie (Julia) in a moment for fun. But yeah with programs like excel you're stuck to whatever the program lets you do.
I will add, btw, that I do find the chart a bit weird. I mean, yeah, the temperature can be between the mean temp and the mean low temp. So you colour that. But the temperature can also totally be outside that range? They are means, after all. It's cleaner than separate representations of the distributions, sure, but it's not *that* intuitive to me. Specifically that's a point against the *stacked* range chart, the normal range chart seems fine (though maybe also better replaced by an actual representation of distribution if that's what's actually happening)
You're forgetting that it must also be integrated in the storage format, which is standardized.
I don't know how extensible it is, but that makes it incompatible with older software anyway.
It must also be supported in the online version of the tool, the collaborative mode and maybe other places.
As a Data Engineer, I very regularly have issues with this chart not existing. Usually for me it's a horizontal version of the chart, where X is time, Y is the categories and Z is phases of those categories. Sort of like a flat Gantt chart. I usually solve it by calculating a field to put the starting point of the charts to their minimum and making that field invisible by filling it with nothing. For example, if your data starts at 10 and goes to 50, I would have a field that has a value of 10 that is at the start of the dataset and has no fill, followed by the actual data.
wow! this makes so much sense!
Let me file a feature request to the Google sheets team (via company internal portal) and see if they can prioritize this.
This is the chart used by meteorologists to plot average monthly low, mean and high temperatures. This is a clear use case to motivate them…
This belongs in the "How did it take decades for basketball to figure out the 3-point shot" bucket, but for software.
What's the stpry behind the 3-point shot? Where all baskets only worth 2 points before then?
@@HanmaHeiro The three point field goal was adopted by the NBA in 1979 for a trial season originally, before being adopted permanently that same season. Before that all shots that weren't a free throw were scored as a 2 point shot.
@randomDisinformation13 wow, had no idea. Thanks!
how did it take them decades to ruin basketball?
"Software" in this case referring to exclusively spreadsheet software, which (is meant to be (lol)) consumer software with a limited selection of simplified charts. Unfortunately it's use is far far more widespread than that in business and government (because it is, to be fair, very useful and easy to use), but those who actually have proper experience making papers or visualisations or so on will be using more advanced software with the ability to make this kind of graph very easily, and customise it in thousands more ways than you can even imagine.
I'm getting semantically satiated by the word "plot". Plot. Plot. Plot.
Lost the plot, eh?
Actually you can modify by hand the numbers on both axis, just its very confusing. I hate that one of the biggest companys in the world cant fix those simple things.
A new form of plot twist! :)
I guess plotting those plots have made you lose the plot.
Oh _that's_ what that's called!?
For your awareness, you can do this in plotly, its just poorly documented - i cant remember the exact argument to pass but asking gpt4 should be able to find it.
Edit: checked the docs and think this from chatgpt should work:
import plotly.graph_objects as go
# Example data
categories = ['Category A', 'Category B', 'Category C']
range1_base = [10, 20, 30]
range1_values = [5, 8, 7] # Heights for the first range
range2_values = [7, 6, 9] # Heights for the second range
fig = go.Figure()
# First bar (stack base)
fig.add_trace(go.Bar(
name='Range 1',
x=categories,
y=range1_values,
base=range1_base,
marker_color='blue'
))
# Second bar (stacked on top of Range 1)
fig.add_trace(go.Bar(
name='Range 2',
x=categories,
y=range2_values,
base=[b + h for b, h in zip(range1_base, range1_values)],
marker_color='orange'
))
# Update layout for aesthetics
fig.update_layout(
barmode='stack',
title='Stacked Range Bar Chart with Custom Base',
xaxis_title='Categories',
yaxis_title='Values',
yaxis=dict(tickmode='linear', range=[0, 50]),
legend_title='Ranges'
)
fig.show()
Sounds a lot like a box and whisker plot, but with some special annotations beyond the traditional median/quartiles .
Should be pretty easy to do exactly what you want in matplotlib :)
What he's describing seems to be intended for any number of gapless, non-overlapping categories in the y-axis.
Sure, it exists, but I don't think I ever needed it. But it certainly wouldn't be very difficult to implement with matplotlib.
I was thinking that too, especially with the weather ones including the record lows and highs. Classic whisker chart territory
This plot type isn't good for the London data (0:19 chart on right). It's not clear for example is the Ontario "Annual mean low" at -9? Or is it at 3? Or is it a range between those two? Basically, it's a bad idea to use a colored in box to plot a single value (here "Annual mean low" is actually at 3 according to table at 0:26)
That's a good point!
Yeah this confuses me as well. Going by the legend, you would think all of the listed temperature minima, maxima and means are singular values, not ranges.
@@Pieter31 Not necessarily, after all, there are many years.
For any given year, you take the average of all daily lows in that year. Since that average won't be the same for all years, you get a range.
At least, I'm assuming that's the case here, but yes, the legend is a bit misleading if this was shown with no context. You might assume it's referring to the lowest annual mean (temperature) otherwise.
@@SaberToothPortilla No, read the table at 0:26, "Annual mean low" is a single number at 3, not a range. The fact that you mistook it for a range is exactly the point I'm trying to make. It's unintuitive and misleading.
I had the same exact confusion/observation, thank you! I would note that this is a pretty common occurrence in filled contour plots discretized into a few color bins, and I have always hated them for that reason.
In excel you can do this with a stacked bar chart. You are very close to being on the right track at 1:36 but you just have to color your blue series transparent. If the data spans across positive to negative ranges, you may need to have two series of the same color that span this range from negative value to zero and then zero to positive value. I think all cases can be covered.
This is very interesting but it's too bad it has to be a hacky workaround and not just one of the options.
@@dragon_nammithere are at least 3 ways he could create the chart in Excel and he gets close to each of them right before saying you can't do that and giving up. For instance you can put any acid labels you want by creating a series with a value for each value you want labeled and the label saying what you want to appear on the axis and make everything invisible about the series except the labels. Or you can have a stacked bar chart not touching the axis by having an invisible layer at the bottom. It's certainly not intuitive to create, but it's not impossible.
What is "Annual mean high"? Specifically, what does the "high" indicate? "Mean" already implies that the result is a scalar, so what's the difference between "Annular mean" and "Annular mean high"?
I assume it means "average maximum daily temperature".
In the us, weather is given to us as a high and low per day. A low of 35F and high of 60F means that ill need a jacket when leaving in the morning and not when I head home
Depends on the quality of the herb.😜
Weather is usually given in a range. The high mean is probably the average of all the high ends of the range
Yeah, here high isn't a qualifier on mean, but rather a noun in itself, it's jargon (Americanism?) for "daily highest temperature", ditto for low.
I'm pretty sure you could do this in R with ggplot. You'd have to do the 'add a big number' trick like at 2:05, but you wouldn't need to photoshop the y-axis labels, you could adjust them within ggplot.
There are a few ways to do so in R. The easiest option is HH::likert(). The other easy options is ggplot() via geom_crossbar(), geom_pointrange(), or geom_linerange(). Then it can be converted from ggplot2 to plotly using plotly::ggplotly(). I believe there are other ways to do so in R, but may not be as efficient.
Toy Example:
df %>%
ggplot() +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = record_max,
ymin = record_min,
),
fill = "darkred",
alpha = 0.1
) +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = mean_max,
ymin = mean_min,
),
fill = "darkred",
alpha = 0.3
) +
geom_crossbar(
aes(
x = x,
y = daily_mean,
ymax = daily_max,
ymin = daily_min,
),
fill = "darkred",
alpha = 0.5
) +
labs(
x = "x",
y = "y"
) +
theme_test()
As a common simpleton:
TOO DAMN MUCH CODE !
[ Hair pluck .gif ]
This reminds me of trying with increasing frustration how to draw a simple box-and-whisker graph in various programs.
To be fair, the best choice for this data would be a box and whiskers plot.
In most cases, but not all. There are some data scenarios where the discrete measurements aren't basically the "min" to "max" that box and whiskers is used for.
Strong disagree. With boxplots you'd miss out on the clear intuition from the colors. And we are not showing a distribution of the monthly temps, rather the key breakpoints in temp from many years worth of monthly temps.
No, a box and whisker plot is about understanding quartiles, which is not what he was trying to do
I was originally thinking this but the very first example is showing several mean temperatures over different time intervals in the same bar. Which isn't the type of dataset you typically pass to a box-whisker plot. You could Frankenstein your own dataset to make it work but that's probably about as far as minute-physics got before they decided to make a video explaining why this is annoying 😅
I came here to say that, but realized b&w only works for exactly 4 values per series, and that's a subset of the cases he's talking about.
It's actually amazing how much you can do with these programs. I have an .xlsx chart that draws filling for wardrobes with sliding doors in 3d. You can set the number of rows (and you can set custom width for each), then define the number of shelves, drawers and hangers per each row, and the table will draw a picture of what it will look like. And it will also calculate the price of course, since this was a project for a furniture retail company. All this was done without VBA or side plug-ins.
It is probably out of scope for most people, but this is so easy to do in Matlab. But if you aren't an engineer you will probably hate it.
GNU Octave might also work then, for those who don't have access.
Box and whisker plot...?
Shows a distribution right? Seems different
@@ikbintom I think that the chart he explains in the video also shows a distribution? It's actually really unclear to me, what is his version of the chart supposed to indicate *exactly*.
Only works for 5 values (Upper limit, upper quartile, median, lower quartile, lower limit), not if yolu have any other number of values
@@squeaksquawk4255 Correct, but that makes his grade example bad, because it is literally just a box plot with exactly 5 values.
@@Bronzescorpion3:17 imagine a set of graphs that the box plot is an element of that set.
For the temperatures by month, plotting it with continuous areas is fine, since you make the assumption that the temperature in one month and the temperature in the next month are related to each other. Which they are, of course. But for different values of London, this range chart does make some more sense.
You can do this pretty easily in a library like matplotlib, in which the vertical or horizontal lines actually require you to pass the start values and lengths of the bars.
Also, it is pretty easily done in a Gantt Chart type of plot, because that parameters are the typical gantt chart input
As far as I can see, the labels you are showing for each shaded region are wrong, the labels actually refer to the edges of the shaded regions, so the charts are not accurate representation of the data. Compare the values from the table at 0:27 and the chart at 0:17. The labels are actually correct in the "wrong" example at 0:52 (line chart before filling it in as an area chart) because the data points correspond to the dots on the lines, and so do the labels. The "fill" simply can't work because you'll get four shaded areas for 5 data points, so using the labels for the shaded areas would simply not match the actual labels in the data.
Precisely!
isn't this really easy to do in matplotlib
Its possible to automate it in Excel without hacks but there's three issues.
1) Always order your categories from highest to lowest.
2) you shouldn't mix positive and negative numbers.
3) You dont need a stacked chart. All you need is a regular bar chart, invert rows/columns and choose overlap 100% in the parameters. It works beautifully for positive numbers and doesnt require math or hacks.
To work with negative numbers you need to order your values from lowest to highest. Obviously there's an issue.
But its quite easy to place a negative chart under a positive chart.
Graphing software like KaleidaGraph or OriginPro can do this. You can probably also do this in Matlab. Spreadsheet software is meant for accountants not science. Excel tends to fall apart when data sets get larger than a few million points.
64-bit Excel with 128GB of RAM does okay… definitely not ideal.
Watching this from London, KY
There is also: London, Kiribati :)
Web developer here! We _did_ actually add that exact kind of chart in the UI Kit we did for one of our customer! It's still a stacked bar chart, but it does _not_ fill the gap between 0 and the next boundary. So I guess it's what you're looking for?
(Except you have to describe the bars: you define where the stack begins, then give the value to each bar inside the stack. If you stack with _n_ values, you have to use the lowest as the start, then create _n_ - 1 bars.)
that's the nice thing about matplotlib - it may be a little more annoying to learn but it lets you create stuff like the range plot :)
It’s neat how many people are confidently asserting incorrect (or incomplete) solutions here. Feels very StackOverflow
and gatekeeping what people comment, very very stackoverflowy
[This comment is closed]
As a wise man once said, the best way to generate online engagement is to post something that contains a mistake. The beauty of the science crowd is that they will perceive mistakes (or the need for improvement) in anything.
@@backwashjoe7864 This is known as Cunninghams law. Rather fun is to refer to this as Murphy's law and someone will come and correct you
I live for this exact variety of programming issue
It's also used in engineering of axes
To look for the tolerance you choose when you try to achieve a specific link between an axe and the hole
Yay London Ontario! Thanks for the shoutout, and for using Celsius for your temperatures!
I think you can do this with math plot lib in Python but not a 100% sure
It may not look as pretty, but you can get the same information from a box plot.
I never realized South America was both north & south of Australia (3:00). I thought Australia & New Zealand were further south. Guess the chart's useful.
The tip of south america very nearly touches antarctica
Havent watched any video of you in years, but this one caught my eye. Kind of crazy how fast the talking/explanation is, but we can play it slowmo 😅
3 area curve charts each average temperature over a year.
Each chart clear colored line.
Now we have a full year for each city clearly. Heck, add blue shading for rainfall or thicker line more rain
That's why I usually program this sort of things in Python, with matplotlib . It's so discrete that the numbers area easily feed into the program and the matplotlib library does the rest. I almost never use spreadsheets.
Stacked Range Bar Chart... this is EXACTLY what I was looking for when I was trying to plot the range of times it takes me to transform each Transformer...
Am I the only one who finds the labels a bit confusing? They look as though they refer to the colored area, but really they refer to the upper bound of the respective area. But I see that its not easy to come up with an intuitive way of labeling the bounds.
draw chart on paint > save as png > import image.
Why not try a variant of a boxplot?
It sounds like a standard percentile/box plot, which is available in any python or R plotting library. yeap, applications like google spreadsheet don’t include many advanced statistical tools, and creating a forest plot in a spreadsheet isn’t very straightforward either.
And there are reasons why typical box plots don’t use grouping across the main measure value (like you have across the y-axis), because it can be confusing. Essentially, you're trying to combine multiple descriptive statistics per group into a single box plot, and that's a bad idea.
Agree it is not directly available in, say, Excel - and it should be - but it can be created without "photoshopping" the axis … as I just made it in Excel in about 5 minutes. It does require "helper / blank" rows to be created.
I have made something similar for the differences, but added 1 more range, plotted in white colour to "remove" the fill between 0 and the first value.
Not dealt with negative values though.
Ps. I was using it to plot speed vs engine RPM for different gears. So the main "box" was for staying within the engine's power band, and then additional boxes for engine speeds below and above the main power band
Never thought I’d see my beloved Excel in a MinutePhysics video!
you can do absolute values if you have negatives and still need totals. you get LOTS of fields but works
I had the exact same problem a few months ago and had to give up and used a similarly weak workaround as you did. Everyone in the comments saying "yeah that should be easy with xyz" should specifically explain how to do it. Because, yes, it SHOULD be easy, but it is one of the problems that looks easy but actually is not. So would be happy to see a tutorial how to do it
For those interested, latex plus tikz pgfplots does support stacked bar plots with negative values both for vertical and horizontal bar layouts. Admittedly, latex is neither a spreadsheet nor a terribly convenient tool to use. Then again, physicists are one of latex's natural user sources.
Thank you. I knew I could rely on someone in the comments confirming that LaTeX can indeed do everything.
2:04 instead of adding an arbitrary number you could instead convert all temperature values to kelvin. Then you would have the same scale but never have negative numbers.
I think this type of chart is one of many that show that charting programs like excel really are insufficient for many charting activities and people should learn more advanced charting software. Matplotlib, ggplot, and HighCharts have been mentioned in comments. I think of it like the difference between using excel vs having an actual database you can get by with excel to a point but eventually everything you try to do with excel will be done better and easier with an actual database.
I do this the whole time. You absolutely can do it but you need to make some invisible series and add some calculations (and depe on exactly how you want it to look, potentially use a second hidden axis on the right. Agree it's too complicated (often what I'm trying to do is a waterfall chart where the bars represent change since the cumulative impact of all the previous bars. It can be done, but agree having it native would be very helpful.
If it's above zero you can add a base area and then change the fill and outline to be transparent
The reason why most automated bar chart programs won't allow this is because of edge cases. For example, if you annual mean is higher than your summer mean high in only one city how do you account for this?
What you're really asking is three different charts on the same scale with the assumption that the numbers are always arranged from lowest to highest. It's a lot more complex than you think.
I have the feeling you can easily get around most if not even all of the issues mentioned here if using a traditional stacked bar chart, at least when not dealing with negative numbers. Add a 'filler' bar between zero and the actual first value and set the color of the 'filler' bar as invisible. Then just modify the range of y-values displayed in the diagram.
You can start stacked charts above zero by adding in an extra Data Series for the initial value but make its colour fill transparent (and hide its data label and title).
There's another chart I never ended up finding an easy way to make, at least in Excel: A 3D scatter plot.
And in general, it would be nice to see more 3D charts in Excel, though I understand the challenge of dealing with 3D rotations.
You can get around the negative number problem by splitting the one that crosses the 0 axis into two entries - one for the negative component and one for the positive component - and just setting them to exactly the same color. It's annoying, but it should at the very least create the visual you're going for?
As for starting above 0, you'd have to make filler sections that span each tick line that match the background color? That one's definitely harder
"Missing a very useful chart type that really shouldnt be missing" is such a frustratingly common experience with all these programs. I dont think I ever got through a single assignment in uni without hitting that particular wall.
In RStudio, type boxplot (Petal.Length ~ Species, data = iris, horizontal = T). This should give you a good starting point.
I always love watching your videos, and I don't want to come off as being overly critical, but...
- The London data are all singular values, rather than ranges, so this type of plot is a bad fit.
- The ranges of latitudes of continents can be easily done in Excel using Up/Down bars.
- The Daylight hours in London all have only positive values and start at 0, so you can just use a stacked column chart.
- The percentiles of grades is a textbook example of where you'd typically use a boxplot.
- The last climate example is pretty much identical to the London example, with the same flaw.
There may very well be examples where stacked range plots like these are the best option, but I did not see any in this video.
Grapher by golden software allows for this with the floating bar chart. I use it to make stratigraphic plots for geology.
I have seen these types of Charts been used in electricity generation. My Home Assistant has this function and uses it to display the energy I got from the grid, from my solar generation and what did go back into the grid from my solar. Also many studies use it to show what type of generation is cheaper (Solar), for example Frauenhofer ISE uses them.
You can still do that for real in Excel if you customize it a bit, but I understand what you mean, it should be easy and it's indeed a very useful type of graph.
This is funny because I was just having this problem yesterday. I have a set of values, some of which are negative, and I want to show how they add up to the total value. In my case, it is profit of various business units that I want to add up to total profit. The main way to show the ups and downs would be a waterfall chart, which are already more difficult than they need to be to make in Excel. However it want to show multiple months or quarters side by side. I ended up just drawing the chart manually because it was taking too long to figure out how to trick Excel.
Seems like working in Kelvin would solve the issue. Though I guess that is covered by the "add a big number" method.
3:08 Yes!! As a teacher, I have tried to present data like this several times. It's really important to be able to visualise the changing composition and values of distributions over time.
Well... Any scientific computing/data science oriented programming language will allow for this type of plot and more to be done easily...
Off the top of my head:
- MATLAB/GNU Octave
- Python
- R
- And many more...
And... I'm fairly certain that the latest version of Excel allows for you to use Python for analysis and visualization directly.
I'm glad to know this chart is actually not supported in Excel, and I'm not just useless at using Excel's unintuitive and frustrating charting interface
You could kind of do this with a box plot - and those are included in most programs. You may need to hack it a bit, but it wouldn’t be too much trouble.
2:36 That cut scared the shit out of me.
The big issue I have excel is it doesn’t let you add individual datapoints to columns. Yes you can add error bars but it’s almost always good to see the individual datapoints especially when there’s not many.
Pretty sure you can do this in minitab, will have to check when I get to the office on Monday
I manage the data visualization system for a data platform for a certain space faring government agency, and our system does support these charts!!
But you might not know it from looking at it as the bar charts will not only accept single values but also ranges. Given the software our system is built in is open sourced I wonder how many other systems already just support ranges.
Just plot a chart with two axis, one being kelvin the other celsius, plot the data using kelvin and set the min range to something reasonable and make the font of the kelvin scale invisible.
The percentile grade distribution is a rather bad example, because it is literally just a box plot turned into bars. Not only can you easily make box plot in those programs I even teach my 8th and 9th graders how to make them and read them.
I was waiting for a Latex or R (programming language) ending to show how to do this. I guess Henry doesn't use those in his day-to-day
I am convinced. And I also have absolute no power to make the changes that you suggest.
Depending on the level of control the library gives, it seems like you could just use a box plot.
on a similar topic, am I the only one trying to make "implication tables" in excel? Like the ones you see on paper maps that show the distances from each city to each city but only lists them one time. I'm constantly making them for inventory information but geez it's a hassle to photoshop around the un-used portion of the table...
Why not a boxplot?
This is just my uneducated first impression, but it seems like the reason this feature is missing from most spreadsheet software is because patent issues rather than any sorts of technical issues. I could be wrong though.
While the developers are listening, all plotting software should make it easy to add a second y-axis with alternate units. For example, a plot of speed vs. time with one y-axis in m/s and a second in kph. It would help those of us often dealing in dual unit systems, but it would have utility way beyond that.
(To clarify: I want two axes for one dataset, not two axes for two datasets on one plot, which nearly every software can already do)
You can put a second data set on the secondary Y axis and scale it to match the primary Y axis.
Libre office devs taking notes for their next update
The amount of physics per minute is unchartable here
After yelling most of day away, cursing about missing user roles, not correct inserted environment variables, 20 minute build times, basic script errors because scripts as to be in a yaml file in a github action, etc. I happy to see other peoples frustrations where I with comfort can say "I will never face that problem".
Tableau did this years ago.
Or see many resources even for Excel. e.g. Jon Peltiers multiple methods for managing this easily in every version of Excel from '95 onwards.
It's just a matter of laying out your data correctly.
It has been a second. But I believe Power BI and Tableau actually have this chart. These are statistical visualization programs and therefore can both do static and dynamic charts.
I think you need a scatter pie plot chart with each point in the scatter plot being a pie chart. Additionally these could be animated which would allow for a third axis (perhaps representing time) so that we can visualize the change of our "scatterpies"
In seriousness - this looks like a fun chart to build as an extension for PowerBI or Looker Studio - I'm guessing Google Sheets could have one but idk about Excel - that place is not fun to do development in...
my dude. that is just a fancy box and whisker plot
Was about to say the same thing.
It makes sense that your profile picture is a cat, since cats love boxes and have whiskers. 😉
Only if you have 5 values per X-axis element. Which some of the examples did, but not all.
As a guy who spends 90% of his day doing data visualization in Excel, I wholeheartedly support this sentiment.
Definetly missing is a proper Histograms, where some groups can be combined because separely the are small, so are combined, but now this total is unfairly represented because it is a wider group. In this we want vertical height considers the width of a bar => area=freq=width x height. So we plot a group showing its width, and the freq. Dens. = freq / group width...
FX stats managed this, but not excel/gopgle sheets/apple number now this is worrysome Geogebra and Desmos.
The 5 points picked for each city seem somewhat arbitrary - why not include the “mean summer low” and “mean winter high”? I suppose because the 5 points picked are guaranteed to be in the same order for each city, whereas “mean summer low” could be hotter or colder than “mean annual high”
I wrote a program ~30 years ago (that I am still working on) for the company I work for, a financial service firm that caters to institutional investors. The program is a tool for analyzing investment performance. I would guess that 85% of the charts made in the program are floating bar charts.
You can make a floating bar in a spreadsheet program by adding a spacer at the bottom and making it invisible. However it is not a straight path from concept to chart, as it should be...
and yes, we have called it a Floating Bar Chart for over 40 years.
Why not make an extension for llibreoffice?