- 96
- 129 219
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.
มุมมอง: 7 710
วีดีโอ
Plotting the Mandelbrot Set | Recursive LAMDBA and COMPLEX Functions in Excel
มุมมอง 282ปีที่แล้ว
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
มุมมอง 135ปีที่แล้ว
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
มุมมอง 218ปีที่แล้ว
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()
มุมมอง 677ปีที่แล้ว
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
มุมมอง 228ปีที่แล้ว
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
มุมมอง 188ปีที่แล้ว
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.4Kปีที่แล้ว
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.6Kปีที่แล้ว
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
มุมมอง 42ปีที่แล้ว
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
มุมมอง 501ปีที่แล้ว
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
มุมมอง 913ปีที่แล้ว
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
มุมมอง 79ปีที่แล้ว
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
มุมมอง 7Kปีที่แล้ว
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
มุมมอง 52K2 ปีที่แล้ว
Plotting Likert (agree/disagree) data in Excel
Self-marking Interactive Excel Worksheets for Teaching | Titration Example
มุมมอง 1592 ปีที่แล้ว
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
มุมมอง 2942 ปีที่แล้ว
The Dumbest Thing to do in a Spreadsheet | 3D Molecules in Excel
Looking Through a 100 Year Old Chemistry Textbook
มุมมอง 2752 ปีที่แล้ว
Looking Through a 100 Year Old Chemistry Textbook
Learning Square Roots from a 1955 Textbook
มุมมอง 432 ปีที่แล้ว
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
มุมมอง 962 ปีที่แล้ว
How to Understand Mendeleev's Periodic Table of 1869
An Excel Hack for Teachers: Generate Feedback Paragraphs Instantly
มุมมอง 1022 ปีที่แล้ว
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
What value i should use in the sequence when my data are in between 0.5-3.0? Your video is awesome but its not working in my data range
=SEQUENCE(25,1,0.5,0.1) will get you a range going from 0.5 to 3.0 at 0.1 intervals instead of something normalised to 100%.
That's it! Now I can complete my mass reduction trans-alignment navigation array.
You flipping legend
THANK YOU <3 OMG
This was such a life saver, thank you so much!!
Can u please put the file somewhere
Can you share this file again?
The box plot, not too thin ? I think it is. (in my opinion 0:02 is better (diagram 2 on right)) amazing tutorial
Hi The link for the download has expired is there anyway to get a copy of the file?
Thank you for making this video
I'm in my last semester of my Masters in organic chemistry. And I will.. to write a research paper, but I'm super confused
Excellent explanation! Thank you! It's a wonder that Excel doesn't do this already.
It can do stacked bar charts that are scaled to 100%. That's perfectly acceptable for most uses, especially if the number of responses is comparable between questions, and works "out of the box" with no modifications. But those can be a little harder to see exactly where the central tendency is. It'll come down to preference and what you want to show.
Thank you so much!! Really helpful and simple to understand!! just used this in some university coursework!
Great video, very straight forward and educational! I have a problem tho, the # doesn't fill the formulas down for me and I couldn't find anything about it on the internet. So I'm not able to fill it down because I'm lazy and don't want to scroll down that far.
Oh never mind it was just loading for a really long time maybe should've listened to you and not used with such big numbers :)
Fantastic! Thank you so much!
Thank you very much, You make it look simple
Simple and helpful. Thank you!!
genius! exactly what I needed, thanks a lot!
Thank you. It took me a while to find as most videos out there are on how to create an initial Likert scale, not on how to display the results. I am grateful that I came across your video!
This is going to completely change work, Thanks man.
i am dumb lmao. could you please elaborate what exactly is happening here? and why there was no reduced mass in the first case
@@ChrisArmstrongChemistry sure that would be great
The reduced mass is essentially a mathematical trick used to account for the accelerations of two bodies exerting a force on each other (constituting an isolated system). If you play with newton's laws of motion (write out the acceleration of one object [call it 2] from the perspective of another [1]) you see that an equation mathematically analogous to (and in fact physically equal to, as can be seen from how it was derived) newton's 2nd law describes their relative interaction, as seen in the frame of [1]. In particular the mass of object [2] is changed to the reduced mass, while the relative force and relative accelerations are unchanged. The purpose of using the reduced mass is to simplify calculations and turn a two body problem into equivalent one body problems, many of which can be solved exactly.
thank you!!! this was so helpful
This is very helpful, thank you so much.
sir, this is amazing, simply amazing.... I need to take a seat...wow!!
This tutorial is excellent. Cheers.
were can i find the game
Excel is amazing!! You can do so much with it :D
Nice video first of all. How would you go about it if you have a reaction and you want to label the different compunds in the reaction. I struggle to get the references right beneath each compund.
How would you visualize a "NA/Don't know" response? Would that be a 2nd axis?
I don't think I've seen N/A used with this style.
Hi. Why does the d3h point group have 2 C3 axes?
It has one C3 axis, but it's labelled in the character table as 2C3 as there are two symmetry operations. You can read that as 120 clockwise and 120 anticlockwise, or 120 degrees and 240 degrees.
@@ChrisArmstrongChemistry I see. Thanks!
Thanks for your help Chris
I don't think I'll ever need to do this, but this is certainly a very comprehensive explanation on how to do this, should I ever need it.
No one should ever do this.
Lol 😂 I like
Thanks for sharing 💥💥
Cool stuff
Thanks brother ,, ♥️👍
Even though I'm a beginner, I was able to follow and learn a lot from this video. Thanks for taking the time to make it!
I find that one of the best ways of learning something is to find the silliest possible use-case and do that. Usually you end up learning some advanced things just by accident.
Wow! This opened my eyes to the dynamic chart, thank you for sharing.
Hoping you can help me. I want to highlight cells a1-d1 if cell e1 equals “apple” OR “pear”. I’m having trouble getting the OR function to work on specific words. I’ve successfully used CF with single words. For example if cell E1 = grey, highlight cells A1-D1 a grey colour. I run into trouble with OR. My formula syntax is…. $E1=OR(“apple”,”pear”). Am I missing something in the syntax or is it a misapplication of the OR function?
OR() needs two or more statements that evaluate as true or false. You need the conditional formatting formula to read something like =OR(E1="apple", E1="pear"), as those statements should evaluate as TRUE or FALSE. You will need to check the use of $, too. Any other issues with AND() and OR() can usually be resolved by using * and + instead, which treats TRUE as 1 and FALSE as 0.
Thank you Chris. You might want to do a video on this, since i was unable to find anyone talking about my scenario. I can’t be the first to have encountered this. Using the OR function keeps my CF simplified and uncluttered…. Trying it out now.
Nice chart. How do you get both ends of the segments to (dynamically) glow? I can see it in the initial chart, but it wasn't covered in the example creation.
Very Confusing
Jeeezz. No comments, 16 likes?! This is really cool Excel stuff! (And PowerPoint as well). Your channel is stronly under-valued.
I likert very much, thank you!
Cool concept, does it break during timewarp?
I don't think anything touching something like this is stable during time warp.
I did a few more tests with this. It's fine to timewarp if it's not moving. If you leave it running on "autopilot", where you click away from it while still moving so that it continues to roll on its own, then even tapping once to 5x timewarp causes it to disappear instantly. I think that must be detecting it as having "crashed" into the surface. You can timewarp at the 2-3x one, where the collisions are still on (which is the only speed allowed if you're still holding one of buttons to move), just fine.
I made a goal this year to get a better understanding of how to use excel and these videos are really helpful, just need to get into the logic mindset for writing the equations.
I tend to throw stuff up randomly as it occurs to me, but if anything might be useful, I suppose I could do requests.
goat!!
ρяσмσѕм 😚