The Chart Missing From ALL Spreadsheet Software

[@SvenSchaffrath86]

This is a bad day at the office turned into a video rant 😂

[@bohdanpyzh2712]

Instead of "this meeting could have been an email" we upgrade to "this meeting could have been a KZbin video rant"

[@UjwalJ211]

So true😂😂

[@BB_Jo-e3z]

Das war ein benfehi! lol

[@VEE727]

It is now a bad day at Google and Microsoft

[@ruroruro]

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.

[@cryme5]

True, but he probably meant without coding

[@ivanjermakov]

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.

[@Splarkszter]

​@@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.

[@ruroruro]

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.

[@casperguo7177]

@@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

[@squeaksquawk4255]

I love how you managed to make "London" into an axis

[@danielnarbett]

And an existential crisis! 😂

[@JosGeerink]

​@@danielnarbettaxistential

[@lukasdon0007]

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.

[@bentfishbowl3945]

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.

[@UMosNyu]

I was thinking if that is not directly in ggplot, maybe there is a way using geom_segment

[@qwerty11111122]

He included plotly in the thumbnail for some reason, plotly has an R/ggplot api...

[@aravamu2]

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.

[@aravamu2]

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()

[@camleee]

The one and only time London Ontario will be mentioned outside of London Ontario

[@adrianburmester1159]

Not Just Bikes also mentions it regularly :)

[@five-toedslothbear4051]

Today I learned about London Kentucky

[@highlorddarkstar]

@@adrianburmester1159but it’s Fake London

[@whoeveriam0iam14222]

@@adrianburmester1159 he's from that fake London so it doesn't count as being outside

[@chrispaxton6580]

I'm a hockey fan from Arizona, I talk about London Ontario from time to time

[@soranuareane]

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.

[@dunglvht]

True, but making this work in matplotlib is a pain

[@sjuns5159]

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)

[@DidierLoiseau]

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.

[@jursamaj]

@@sjuns5159 From 3:10 to 3:20, he shows what he wants: record low, mean minimum, daily mean minimum, daily mean, daily mean max, mean max, and record high. The record low & high cover the full range, with details in between. Showing the actual distributions would involve something much like violin charts, which apparently a bunch of people hate.

[@michelkluger]

Talk is cheap, create a PR

[@DanKaschel]

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.

[@chylld]

This is what I do as well. Invisible first category with a common minimum, and deltas thereafter

[@christiannorf1680]

The problem remains that it is a hacky workaround

[@Elesario]

@@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 😉.

[@mskiptr]

​@@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.

[@NeatNit]

What if you want to start with a negative number?

[@wergeldexcimer]

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.

[@ReynaMirez]

why do we need to know that you've been watching these videos for a long time?

[@tommeng6522]

@@ReynaMirez He's trying to grab attention

[@HelenoPaiva]

Your solution probably works, but the point he is trying to make is that it isn’t a simple ordeal to do on most common spreadsheet apps. Let’s be real- you can calculate qui squared values on excel, there is a formula for it, but it’s clumsy and a pain to use.. at a given point you just want to hop into R and do it quickly and easily.

[@paprikaspicey]

​@@ReynaMirezwhy do we want to know your thoughts on their comment?

[@uhateme]

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.

[@andrewmole745]

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…

[@terry_the_terrible]

I did some prototyping on EXCEL and I got a reliable chart that works in literally every situation. No hacks, photoshop or funny business. It's all in the data prep. Data prep: 2m, Execution 2m. Complexity: Low. Reliability: high 1) Prepare the original table with only the positive numbers from highest to lowest. ( + Summer mean high 27 24 30 + Annual mean high 13 16 20 + Annual mean 8 12 14 + Annual mean low 3 8 12 + Winter mean low) 0 3 0) 2) Copy paste the same table categories right underneath with only the negative numbers from lowest to highest. ( - Winter mean low -9 0 -3 - Annual mean low 0 0 0 - Annual mean 0 0 0 - Annual mean high 0 0 0 - Summer mean high 0 0 0) 3) Click on this table, create a regular bar chart. 4) In series options, choose series overlap 100%. 5) (Optional) Make the categories with the same name have the same chart colour. No hacks, photoshop, math or screen capture required. The data overlap already calculates the intervals. FAQ: Q: Terry, that's a hack. A: For every advanced table to make sense you need to order your data. It's the same here. Q: Why can't Excel just give me this basic chart? What's wrong with them? A: You just need to order your data and overlap the series. There's a glitch in Excel where the first category is always the rectangle at the back but it fucks up once it's a negative number. They could fix it, but it's such a niche interaction. Q: Why can't Google Sheets just give me this basic chart? What's wrong with them? A: You can't overlap series in Google sheets without an add-on. It's still a great free tool.

[@sourcererseven3858]

It's been six days. What's the status? 😆 Oh and happy Thanksgiving I guess... GET BACK TO WORK, WE NEED THAT CHART!

[@davidli9887]

List I am mad you didn't mention R when listing out statistical software. 0:11

[@davidli9887]

Box-wisker chart works well here 😊

[@fronbasal]

Same here 😂

[@sakinano99]

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)

[@1CO1519]

That's a good point!

[@Pieter31]

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.

[@SaberToothPortilla]

​@@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.

[@sakinano99]

@@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.

[@SSJ3Tim]

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.

[@AngryArmadillo]

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 :)

[@sebastianjost]

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.

[@kaitlyn__L]

I was thinking that too, especially with the weather ones including the record lows and highs. Classic whisker chart territory

[@olivierernoult895]

Yes exactly ! Whisker plots are the way to display those type of data ( average, min max etc...) they are far more clearer than the one he created, as bar plots are never used to represent maximum and minimum value.

[@MrMlanghornes]

Yep, came here to say this. Box plot gives a better idea of mean, IQR, and outliers at a glance

[@WTJBlog]

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.

[@dragon_nammi]

This is very interesting but it's too bad it has to be a hacky workaround and not just one of the options.

[@andrewm512]

​@@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.

[@terry_the_terrible]

I did some prototyping on EXCEL and I got a reliable chart that works in literally every situation. No hacks, photoshop or funny business. It's all in the data prep. Data prep: 2m, Execution 2m. Complexity: Low. Reliability: high 1) Prepare the original table with only the positive numbers from highest to lowest. ( + Summer mean high 27 24 30 + Annual mean high 13 16 20 + Annual mean 8 12 14 + Annual mean low 3 8 12 + Winter mean low) 0 3 0) 2) Copy paste the same table categories in inverse order right underneath with only the NEGATIVE numbers. ( - Winter mean low -9 0 -3 - Annual mean low 0 0 0 - Annual mean 0 0 0 - Annual mean high 0 0 0 - Summer mean high 0 0 0) 3) Click on this table, create a regular bar chart. 4) In series options, choose series overlap 100%. 5) (Optional) Make the categories with the same name have the same chart colour. No hacks, photoshop, math or screen capture required. The data overlap already calculates the intervals. FAQ: Q: Terry, that's a hack. A: For every advanced table to make sense you need to order your data. It's the same here. Q: Why can't Excel just give me this basic chart? What's wrong with them? A: You just need to order your data and overlap the series. There's a glitch in Excel where the first category is always the rectangle at the back but it fucks up once it's a negative number. They could fix it, but it's such a niche interaction. Q: Why can't Google Sheets just give me this basic chart? What's wrong with them? A: You can't overlap series in Google sheets without an add-on. It's still a great free tool. Q: Stacked bar charts! Invisible box line underneath. A: Stacked bar charts need the user to calculate the temperature difference which defeats the purpose of this simple type of chart.

[@mateuszobara]

@@dragon_nammi working in excel is 95% hacky workarounds and its great

[@MinuteEarth]

I'm getting semantically satiated by the word "plot". Plot. Plot. Plot.

[@emptytent]

Lost the plot, eh?

[@juanitogalera5033]

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.

[@backwashjoe7864]

A new form of plot twist! :)

[@Valgween]

I guess plotting those plots have made you lose the plot.

[@cheeseburgermonkey7104]

Oh _that's_ what that's called!?

[@aravamu2]

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()

[@PrograError]

As a common simpleton: TOO DAMN MUCH CODE ! [ Hair pluck .gif ]

[@naveengta]

This belongs in the "How did it take decades for basketball to figure out the 3-point shot" bucket, but for software.

[@HanmaHeiro]

What's the stpry behind the 3-point shot? Where all baskets only worth 2 points before then?

[@randomDisinformation13]

​@@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.

[@HanmaHeiro]

@randomDisinformation13 wow, had no idea. Thanks!

[@richardfecteau4490]

how did it take them decades to ruin basketball?

[@randomguy555]

"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.

[@MaxArt2501]

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.)

[@scope40k]

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.

[@DataCraftsman]

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.

[@jursamaj]

How well does that handle all-positive, all negative, and mixed bars? I tried whipping up something much like that in Open Office, and the results were… unpleasant. For all-positive, the categories stack upward. For all-negative, they stack downward. For mixed, the negatives stack downward, then the postives stack upward. So the colors of the chart come out all wrong.

[@terry_the_terrible]

I just did it in MS Excel in 2m without an invisible stack. I understand your Gantt chart method but it requires the additional step of converting temperatures into temperature differences and it doesn't solve the issue of negative values. This video mixes positive and negative numbers which makes the plotting hard but you can circumvent all the issues in this video by: 1) Prepping your data first. 2) Overlapping series. Which removes the need to calculate temperature differences. 3) Separating the negative and positive values into two overlapping charts.

[@DataCraftsman]

@@terry_the_terrible Ah yeah I forgot you have to also calculate the differences. I haven't done it in ages. It's not a fun experience!

[@DataCraftsman]

@@jursamaj It probably doesn't handle any negatives. I can't remember if I ever tried it with negative values. You also have to calculate the differences in values as terry said.

[@StefanoBorini]

To be fair, the best choice for this data would be a box and whiskers plot.

[@DaneRossenrode]

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.

[@BenitoAndito]

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.

[@error.418]

No, a box and whisker plot is about understanding quartiles, which is not what he was trying to do

[@mrmr3141]

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 😅

[@JackEnneking]

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.

[@slarson9483]

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.

[@qnxvr576]

64-bit Excel with 128GB of RAM does okay… definitely not ideal.

[@therongjr]

This reminds me of trying with increasing frustration how to draw a simple box-and-whisker graph in various programs.

[@sciencetube4574]

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.

[@PMX]

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.

[@mskiptr]

Precisely!

[@vinceb8041]

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 :)

[@JustinB-123]

Watching this from London, KY

[@hanswoast7]

There is also: London, Kiribati :)

[@christianlabanca5377]

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

[@sugoisenpai6565]

To get around the x axis being cumulative, you can add in another variable & set the colour to white, to give you the "bump" above the x axis as needed. So for your +3 example, add in another variable, set it to 3, then do the rest as per usual and it should work!

[@xlr8436]

I never really thought about it til this video, and it makes total sense! Would be trivial to add to programs as it’s super basic math/programming. I’ve used these kind of charts before and I came up with a hack though. I reference evening off a value, and then I just create a column (or row) with that value as the first number . Then I just set the colour to white or transparent on the graph

[@ruroruro]

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"?

[@Redingold]

I assume it means "average maximum daily temperature".

[@qwerty11111122]

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

[@thomasgoodwin2648]

Depends on the quality of the herb.😜

[@Jaice00]

Weather is usually given in a range. The high mean is probably the average of all the high ends of the range

[@RaichuKFM]

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.

[@kenten]

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...

[@beng4186]

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.

[@withjoe1880]

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.

[@isaiahnordine3058]

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.

[@Yakes99]

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.

[@WoWOmegor]

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()

[@AngelEduardoLopezZambrano]

I'm a software developer for a service company, my job is to create internal tools for managing the work orders. I recently faced this problem when trying to display min, max and avg from a large dataset pivoted by different groups. I know exactly what you're talking about, for even libraries that are supposed to let you create graphs programmatically don't have this option. I don't have negative numbers so for me it was as simple as adding an extra column size min and make it transparent. The rest of the columns I had to subtract avg - min and max - avg. I feel your pain

[@SLOFear]

Box and whisker plot...?

[@ikbintom]

Shows a distribution right? Seems different

[@ruroruro]

​@@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*.

[@squeaksquawk4255]

Only works for 5 values (Upper limit, upper quartile, median, lower quartile, lower limit), not if yolu have any other number of values

[@Bronzescorpion]

@@squeaksquawk4255 Correct, but that makes his grade example bad, because it is literally just a box plot with exactly 5 values.

[@joaovmlsilva3509]

​@@Bronzescorpion3:17 imagine a set of graphs that the box plot is an element of that set.

[@mikoajp.5890]

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.

[@keyboardtalk]

Thank you. I knew I could rely on someone in the comments confirming that LaTeX can indeed do everything.

[@jlchappell]

Yay London Ontario! Thanks for the shoutout, and for using Celsius for your temperatures!

[@acemcbeanpiff]

I live for this exact variety of programming issue

[@curtiswfranks]

What about a box-and-whisker plot? It is kinda close.

[@sacdesable7971]

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

[@Gistedkudu]

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.

[@M19pickles]

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.

[@WahPony]

fig.add_shape( type="rect", x0=0, y0=0, x1=1, y1=1, fillcolor="blue", opacity=0.6, line_color="black" ) in plotly in python

[@konthoofd4]

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 😅

[@vikhattangady]

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

[@mymanager3662]

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.

[@echan101]

I've done these graphs in Excel by using transparent filled areas and it works with negative values too. It was for voltage ranges of integrated circuits to visually show that they were compatible. So it's definitely possible.

[@SergiiNechuiviter]

Did you try candles to represent this information?

[@EugenioArellano-w7b]

This is probably not that hard in any of these software, but having these seemingly simple things hidden behind a configuration trick or a hack is the reason I learned to use D3.js which still has the most reasonable balance between low level and friendly(ish) API

[@gedece]

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.

[@harleykf1]

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.

[@ThingyMcThingson]

I was very curious going into this video, however I was not expecting you to talk about the exact type of chart I’ve literally just (last week!) written a VBA script to generate in Excel 😅

[@BiffBruise]

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.

[@Magmafrost13]

"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.

[@yc1094]

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.

[@lilywater3683]

The only reason stacked bar chart doesn’t work because zero has no significance in Celsius in the context of temperature. Try using Kelvin

[@DaxSudo]

Quadratic is an open source excel type program that allows python to be run directly it can absolutely be used to make these charts

[@nathanbanks2354]

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.

[@illusion466]

The tip of south america very nearly touches antarctica

[@Bronzescorpion]

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.

[@rrm2160]

Grapher by golden software allows for this with the floating bar chart. I use it to make stratigraphic plots for geology.

[@KaliFissure]

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

[@Jan12700]

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.

[@AndrewLaw87]

This would work great for Gantt charts, or other time-based charts showing how events start and end (a range of time values). SO many hacks required to make a Gantt chart in Excel.

[@DeathlyTired]

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.

[@asdfghyter]

what about box charts? aren’t they a standard way of showing the same data?

[@IRNoahBody]

you can do absolute values if you have negatives and still need totals. you get LOTS of fields but works

[@the-karabin]

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.

[@jezusbloodie]

Labelling at the item/object/boundary instead of in the ledger is the only thing I can think of that is intuitive, but it's also way less efficient, spacing-wise, and more cluttersome

[@mellowfish316]

It’s neat how many people are confidently asserting incorrect (or incomplete) solutions here. Feels very StackOverflow

[@l9day]

and gatekeeping what people comment, very very stackoverflowy

[@null-0x]

[This comment is closed]

[@backwashjoe7864]

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.

[@henryginn7490]

@@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

[@TheRossWilliams]

There is a box and wisker plot in Excel, seems along the lines of what you want if you have the source data, not just summary parameters/staistics

[@joeyoest1105]

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.

[@bloomtwig76]

Pretty sure you can do this in minitab, will have to check when I get to the office on Monday

[@AdrianHereToHelp]

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

[@elcucumber2847]

my dude. that is just a fancy box and whisker plot

[@sufianhaq]

Was about to say the same thing.

[@agiar2000]

It makes sense that your profile picture is a cat, since cats love boxes and have whiskers. 😉

[@rtg_onefourtwoeightfiveseven]

Only if you have 5 values per X-axis element. Which some of the examples did, but not all.

[@TheDelwish]

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.

[@xfgjnsfgj]

Seems like working in Kelvin would solve the issue. Though I guess that is covered by the "add a big number" method.

[@TimRobertsen]

You have my support

[@SamuelJSAdamsI]

If it's above zero you can add a base area and then change the fill and outline to be transparent

[@camfunme]

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).

[@cubfan84]

I needed this plot during my PhD! I had to bootstrap code in Python's MatPlotLib just to make it. It was so damn difficult and was never worth it.

[@AlisterPuddifer]

You know what else they don't do? A pie chart made from concentric circles. It would be super useful for showing the distribution of layers in non-flat objects, like planets, onions, cells, etc.

[@YoungPineTree]

In RStudio, type boxplot (Petal.Length ~ Species, data = iris, horizontal = T). This should give you a good starting point.

[@benjixlegaulois5000]

Why not try a variant of a boxplot?

[@gadimus]

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...

[@gniludio]

2:36 That cut scared the shit out of me.

[@jursamaj]

I've never been much into bar charts, let alone stacked ones, but this video inspired me to see how Open Office handles the attempt. The results were… unpleasant. For all-positive, the categories stack upward. For all-negative, they stack downward. For mixed, the negatives stack downward, then the postives stack upward. So the colors of the chart come out all wrong.

[@jursamaj]

Ah-hah! I found a way to do it in OpenOffice that is perfect, except without the city labels. In the 1st row of the table, put a 1, then the minimum value of each block for city #1. In the 2nd row, put a 1, then the maximum of each block for city #1. Note, these 2 rows contain mostly the same value. Leave the 3rd row blank. The 4th, 5th, and 6th rows are the same, but for city #2. Likewise, groups of 3 rows for each city. Now, select the whole table, and create an XY(scatter) chart. In the chart, select each data series and set its line width as you like, its symbol size to 0, and its missing value to 'leave gap'. For the X axis, turn off the numeric labels. Maybe there's a way to get the city names in at this point, but I'm about to head to work, so I leave that as an exercise for the … student. 😀 I assume Excel and other spreadsheets have all the same options, possibly with different names, but it should all translate over.

[@Khether0001]

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.

[@TheBoogerJames]

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.

[@njdinostar]

yes! I want this feature! isn't it very similar to a box plot? I feel like a bar chart is to this stacked columns chart as a histogram is to a boxplot.

[@rharbarenko]

Woot someone talked about London Ontario!! if only as an example.... 😃

[@BR0THERR0SS]

Thank you

[@ShinSpiegel]

I love this video :)

[@drandydoesanalytics]

Love it! Another chart that doesn’t seem to be easy to do in Excel/R etc is icon arrays, no way so far as I can see.

[@Oh_Sully]

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.

[@thebeardman7533]

I think you can do this with math plot lib in Python but not a 100% sure

[@samlippey3435]

The most relevant youtube video. It's here, we found it

[@TonksMoriarty]

Omfg, I was attempting to create a timeline of comic books and their releases, and couldn't for the life of me find an appropriate graph for it. What I was looking for was this graph.

[@jacen60]

I mean, I guess adding a big number is indeed the solution for this. maybe you could then programmatically choose to override the *display* of the vertical axis numbers. like tell it to use these numbers to make the graph but show these other numbers. I feel like maybe that could be possible with matplotlib/numpy but it's been a while since I used it...I should try it some time.