- 96
- 136 868
Chris Armstrong
United Kingdom
Приєднався 11 гру 2011
For dumping some chemistry and non-chemistry related content - along with a collection of the weirdest and dumbest things to do in a spreadsheet.
Violin Plots in Excel (without plug-ins...) | LAMBDA(), BYROW(), and Kernel Density Estimation
[Slip to about 9:25 for the formula if you don't need the explanation of how to put it together.]
Have you wanted to implement a violin plot as a more visually intuitive alternative to a box and whisker chart? But found that you've only got your favourite cursed accountancy programme to make it? Annoyed that the built-in box plot option is actually a bit rubbish?
You should probably do it properly with actual statistics software...
But that's lame and boring. So, instead, you can do Kernel Density Estimation (KDE) on your data by using BYROW() to work out the density of your data at each point in your range by summing NORM.DIST() over your data. It's that easy. Mostly.
Have you wanted to implement a violin plot as a more visually intuitive alternative to a box and whisker chart? But found that you've only got your favourite cursed accountancy programme to make it? Annoyed that the built-in box plot option is actually a bit rubbish?
You should probably do it properly with actual statistics software...
But that's lame and boring. So, instead, you can do Kernel Density Estimation (KDE) on your data by using BYROW() to work out the density of your data at each point in your range by summing NORM.DIST() over your data. It's that easy. Mostly.
Переглядів: 8 795
Відео
Plotting the Mandelbrot Set | Recursive LAMDBA and COMPLEX Functions in Excel
Переглядів 342Рік тому
I've been trying to get my head around recursive functions for the last few weeks. I remain convinced that this is actually an undocumented feature rather than an intended use for LAMBDA(), but it works. Anyway, here's how to plot a fractal in a spreadsheet. It actually calculates faster than you might expect, but it remains awkward and difficult because the COMPLEX() functions and IMSUM() and ...
Pixel Art Health Bar in Excel | A Surprising Use For Recursive LAMBDA() Functions
Переглядів 148Рік тому
This covers how to use HSTACK recursively to generate an array of arrays. In this context, doing some very silly pixel art to make a retro video game style health bar using conditional formatting. There may be legitimate uses for this, I don't know, I'm just here to annoy proper programmers by doing wileird things that this cursed accountancy software was definitely not designed to do. If there...
Bubbling Liquid | An Animated Infographic in Excel
Переглядів 229Рік тому
This is very, very silly. Spreadsheets were not meant for this sort of thing. You should absolutely under no circumstances ever do this. But if you find a legitimate use, let me know. One thing I added after recording is that you can set the number of bubbles (rows) to be a function of the height of the graph. So if it's nearly empty, you have fewer, keeping the density of them similar. That wi...
The Collatz Conjecture in a Spreadsheet | Introduction to Recursive LAMBDA()
Переглядів 732Рік тому
The Collatz conjecture states that if a number is even, we divide it by two, and if it is odd, we multiply by three and add one, and then if this continues, it should always reach 1. This has been tested to obscenely high numbers by professionals. Yet the mathematical proof that this is always true is, so far, frustratingly outside of modern mathematics. No, we can't do that in Excel. Or, at le...
Light-up Infographic / Graphic Equaliser in Excel
Переглядів 260Рік тому
Fancy dashboards are all the rage these days... but how fancy can we get? In this video, I'll cover a really silly looking thing that involves blocking data, and conditionally formatting it on a chart to highlight the highest values. This can be easily expanded to highlight any others such as ones breaking a threshold, or low ones just use your imagination and a bit of belligerence.
You Can (But Probably Shouldn't...) Visualise Atomic Orbitals in Excel
Переглядів 201Рік тому
I've been doing this trick for a while to help visualise atomic and even molecular orbitals. There are many, many ways to extend it. And it turns out that dynamic arrays make it a lot faster and easier. Things you can extend it with, but aren't properly covered here: * Add the functions to named ranges to make things much cleaner and easier to read. * Use spin buttons to change the angle of the...
Minesweeper in Excel (with no VBA...)
Переглядів 1,6 тис.Рік тому
This is a mostly-functional Minesweeper without the need for VBA. Compromises must be made for that requirement, however! But, you can get all of the game mechanics done natively. All the additional code is required for is sorting out the input properly. It's playable without it, but not the best. Anyway... it's a fun little problem to solve.
Conditional Formatting for Graphs and Charts in Excel
Переглядів 1,7 тис.Рік тому
You cannot natively add conditional formatting to chart objects in Excel. Maybe someone can bug them about that. Until then, this is one of a number of hacks you can do to highlight and change the colour of charts based on the value.
Articulated Lunar Rover | Spaceflight Simulator
Переглядів 43Рік тому
If you build a big ground vehicle in SFS, you can struggle to get over some obstacles as the wheels are fixed in place. The solution: three vehicles joined as a train, using a big rover wheel and a box made of parachute components to create a hinge mechanism. That creates a train, but pulling it requires a lot of power. So, by glitch it design, if you're still holding the left/right keys to dri...
Progress Bar in Excel | A Conditionally Formatted Donut Chart
Переглядів 513Рік тому
Excel doesn't support conditional formatting of charts natively. That's annoying. So if you want a big "tick" to say someone has successfully completed something, you might need to make concessions with its formatting or do a little more work. There are a few other ways of doing this, I might suggest doing the logical statement in a separate cell, and referencing that in the IF statements, but ...
Spreadsheets as Code | FizzBuzz in Excel 365
Переглядів 927Рік тому
This video discusses how to output the "fizz-buzz" game in a spreadsheet, producing 1, 2, fizz, 4, buzz, fizz, 7, 8... and so on. But it's not really about getting the right answer, it's about looking at how you approach the problem. This is one solution of many. It might not even be the best. But the main takeaway point is not getting the right answer: it's about how you structure it, how you ...
If QI was made in 1856 -- Popular Errors; Explained and Illustrated
Переглядів 81Рік тому
This is Popular Errors; Explained and Illustrated, by John Timbs. It's about 90% common misconceptions, corrected with some sound science (though confusing English...) and 10%... errm.... errr....... yes.
Illuminated sci-fi-style displays and infographics in Excel
Переглядів 7 тис.Рік тому
Using an Excel Spreadsheet to create a sci-fi display, which looks like sequentially illuminating LEDs. This video mostly focuses on the set-up needed to make the display behave correctly and dynamically, with some extra special tacky glowing effects added! There's largely no point to this, as always. Unless you really want to dazzle and confuse someone with an interactive dashboard. I use FLOO...
Plotting Likert (agree/disagree) data in Excel
Переглядів 55 тис.2 роки тому
Plotting Likert (agree/disagree) data in Excel
Interactive Titration Diagram in Excel
Переглядів 1882 роки тому
Interactive Titration Diagram in Excel
Self-marking Interactive Excel Worksheets for Teaching | Titration Example
Переглядів 1622 роки тому
Self-marking Interactive Excel Worksheets for Teaching | Titration Example
Doing SpaceX Sh*t | Landing a Falcon 9 in Spaceflight Simulator
Переглядів 292 роки тому
Doing SpaceX Sh*t | Landing a Falcon 9 in Spaceflight Simulator
The Dumbest Thing to do in a Spreadsheet | 3D Molecules in Excel
Переглядів 3022 роки тому
The Dumbest Thing to do in a Spreadsheet | 3D Molecules in Excel
Looking Through a 100 Year Old Chemistry Textbook
Переглядів 2902 роки тому
Looking Through a 100 Year Old Chemistry Textbook
Learning Square Roots from a 1955 Textbook
Переглядів 452 роки тому
Learning Square Roots from a 1955 Textbook
Sampling chemical kinetics data with SEQUENCE() and dynamic arrays in Excel 365
Переглядів 732 роки тому
Sampling chemical kinetics data with SEQUENCE() and dynamic arrays in Excel 365
How to Understand Mendeleev's Periodic Table of 1869
Переглядів 1022 роки тому
How to Understand Mendeleev's Periodic Table of 1869
An Excel Hack for Teachers: Generate Feedback Paragraphs Instantly
Переглядів 1312 роки тому
An Excel Hack for Teachers: Generate Feedback Paragraphs Instantly
How to stop Excel treating everything as a date
Переглядів 292 роки тому
How to stop Excel treating everything as a date