Chris Armstrong
Chris Armstrong
  • Видео 96
  • Просмотров 122 979
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.
Просмотров: 6 812

Видео

Plotting the Mandelbrot Set | Recursive LAMDBA and COMPLEX Functions in Excel
Просмотров 253Год назад
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
Просмотров 127Год назад
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
Просмотров 206Год назад
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()
Просмотров 639Год назад
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
Просмотров 216Год назад
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
Просмотров 177Год назад
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,3 тыс.Год назад
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,5 тыс.Год назад
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
Просмотров 498Год назад
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
Просмотров 911Год назад
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
Просмотров 78Год назад
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
Просмотров 49 тыс.Год назад
Plotting Likert (agree/disagree) data in Excel
Interactive Titration Diagram in Excel
Просмотров 1802 года назад
Interactive Titration Diagram in Excel
Self-marking Interactive Excel Worksheets for Teaching | Titration Example
Просмотров 1562 года назад
Self-marking Interactive Excel Worksheets for Teaching | Titration Example
Better Charts and Error Bars in Excel
Просмотров 4452 года назад
Better Charts and Error Bars in Excel
Doing SpaceX Sh*t | Landing a Falcon 9 in Spaceflight Simulator
Просмотров 292 года назад
Doing SpaceX Sh*t | Landing a Falcon 9 in Spaceflight Simulator
Final Frozen 7 -- Elsa vs Water Spirit
Просмотров 1412 года назад
Final Frozen 7 Elsa vs Water Spirit
The Dumbest Thing to do in a Spreadsheet | 3D Molecules in Excel
Просмотров 2892 года назад
The Dumbest Thing to do in a Spreadsheet | 3D Molecules in Excel
Looking Through a 100 Year Old Chemistry Textbook
Просмотров 2582 года назад
Looking Through a 100 Year Old Chemistry Textbook
Learning Square Roots from a 1955 Textbook
Просмотров 432 года назад
Learning Square Roots from a 1955 Textbook
Creating Wordle in Excel (Part 2)
Просмотров 5412 года назад
Creating Wordle in Excel (Part 2)
Sampling chemical kinetics data with SEQUENCE() and dynamic arrays in Excel 365
Просмотров 712 года назад
Sampling chemical kinetics data with SEQUENCE() and dynamic arrays in Excel 365
How to Understand Mendeleev's Periodic Table of 1869
Просмотров 932 года назад
How to Understand Mendeleev's Periodic Table of 1869
Creating Wordle in Excel
Просмотров 4,1 тыс.2 года назад
Creating Wordle in Excel
An Excel Hack for Teachers: Generate Feedback Paragraphs Instantly
Просмотров 912 года назад
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
Tab stops | Aligning CV headers
Просмотров 382 года назад
Tab stops | Aligning CV headers

Комментарии

  • @tejas.a06
    @tejas.a06 Месяц назад

    You flipping legend

  • @samb5282
    @samb5282 Месяц назад

    What about where 'Neither agree nor disagree' is zero?

  • @MiniMeags
    @MiniMeags Месяц назад

    THANK YOU <3 OMG

  • @carmenalns4156
    @carmenalns4156 Месяц назад

    This was such a life saver, thank you so much!!

  • @noobkolni9648
    @noobkolni9648 2 месяца назад

    Can u please put the file somewhere

  • @jgeekw
    @jgeekw 2 месяца назад

    Can you share this file again?

  • @conscientunit1157
    @conscientunit1157 2 месяца назад

    boycott violin plots

  • @watchingwolf8092
    @watchingwolf8092 3 месяца назад

    The box plot, not too thin ? I think it is. (in my opinion 0:02 is better (diagram 2 on right)) amazing tutorial

  • @Seacowdaz
    @Seacowdaz 3 месяца назад

    Hi The link for the download has expired is there anyway to get a copy of the file?

  • @matthewrater5309
    @matthewrater5309 4 месяца назад

    Thank you for making this video

  • @Hutrzqbh
    @Hutrzqbh 4 месяца назад

    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

  • @jameselliott4911
    @jameselliott4911 4 месяца назад

    Excellent explanation! Thank you! It's a wonder that Excel doesn't do this already.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry 4 месяца назад

      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.

  • @yourlonglosttwin5287
    @yourlonglosttwin5287 4 месяца назад

    Thank you so much!! Really helpful and simple to understand!! just used this in some university coursework!

  • @matiaskoskinen5672
    @matiaskoskinen5672 5 месяцев назад

    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.

    • @matiaskoskinen5672
      @matiaskoskinen5672 5 месяцев назад

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

  • @eonswan
    @eonswan 5 месяцев назад

    Fantastic! Thank you so much!

  • @adinanibnmubarak1410
    @adinanibnmubarak1410 6 месяцев назад

    Thank you very much, You make it look simple

  • @tgregg75
    @tgregg75 6 месяцев назад

    Simple and helpful. Thank you!!

  • @carok6673
    @carok6673 6 месяцев назад

    genius! exactly what I needed, thanks a lot!

  • @mlo9987
    @mlo9987 7 месяцев назад

    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!

  • @letsdoathing9568
    @letsdoathing9568 7 месяцев назад

    This is going to completely change work, Thanks man.

  • @someone-kw3ou
    @someone-kw3ou 9 месяцев назад

    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
      @ChrisArmstrongChemistry 8 месяцев назад

      This is a demo/test animation that has mysteriously inserted itself into search results. If you need to know reduced mass, I'd suggest either textbooks or HyperPhysics.

    • @someone-kw3ou
      @someone-kw3ou 8 месяцев назад

      @@ChrisArmstrongChemistry sure that would be great

    • @Onoesmahpie
      @Onoesmahpie День назад

      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.

  • @yumnamoussa8165
    @yumnamoussa8165 9 месяцев назад

    thank you!!! this was so helpful

  • @Yoonsoo-tx8my
    @Yoonsoo-tx8my 9 месяцев назад

    This is very helpful, thank you so much.

  • @spilledgraphics
    @spilledgraphics 9 месяцев назад

    sir, this is amazing, simply amazing.... I need to take a seat...wow!!

    • @spilledgraphics
      @spilledgraphics 9 месяцев назад

      minute 13....is so eye opening. Hat off!

  • @Bob-606
    @Bob-606 10 месяцев назад

    This tutorial is excellent. Cheers.

  • @user-co7fd8gr4y
    @user-co7fd8gr4y 11 месяцев назад

    were can i find the game

  • @tally3067
    @tally3067 Год назад

    Excel is amazing!! You can do so much with it :D

  • @Nurnochscheissnamen
    @Nurnochscheissnamen Год назад

    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.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      I would try to keep it in ChemDraw or similar where possible if it's more complicated than just providing a figure caption.

  • @AnnaRiling
    @AnnaRiling Год назад

    How would you visualize a "NA/Don't know" response? Would that be a 2nd axis?

  • @felipeleria2796
    @felipeleria2796 Год назад

    Hi. Why does the d3h point group have 2 C3 axes?

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      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.

    • @felipeleria2796
      @felipeleria2796 Год назад

      @@ChrisArmstrongChemistry I see. Thanks!

  • @hs0008
    @hs0008 Год назад

    Thanks for your help Chris

  • @DissectingThoughts
    @DissectingThoughts Год назад

    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.

  • @alexcool879
    @alexcool879 Год назад

    Lol 😂 I like

  • @spen2431
    @spen2431 Год назад

    Thanks for sharing 💥💥

  • @alexcool879
    @alexcool879 Год назад

    Cool stuff

  • @AdityaJha02
    @AdityaJha02 Год назад

    Thanks brother ,, ♥️👍

  • @wildcard9839
    @wildcard9839 Год назад

    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!

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      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.

  • @steves6557
    @steves6557 Год назад

    Wow! This opened my eyes to the dynamic chart, thank you for sharing.

  • @ericl2851
    @ericl2851 Год назад

    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?

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      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.

    • @ericl2851
      @ericl2851 Год назад

      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.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      You might find it useful to try: =NOT(ISERROR(MATCH($E$1,$F$1:$F$2,0))) Where F1:F2 contains your list, "apple", "pear". Here, it looks for it in a list, and if it isn't found, it returns an error. So the NOT() ISERROR() combination will return TRUE if it's found. That way, you can extend your choices to >2 entries more easily. Especially if you replace $F$1:$F$1 with a dynamic array that automatically counts and expands to the right number of entries, which can be done with OFFSET(). So if you add "banana" to F3, it would expand to include that. Obviously, stash that somewhere innocuous, but easy to access.

  • @replikuh
    @replikuh Год назад

    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.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      Good point. I hadn't noticed the difference. My main guess is that it's the order that the parts are rendered in, so swapping the order of the series and which is on which axis probably will change that.

    • @replikuh
      @replikuh Год назад

      @@ChrisArmstrongChemistry Ah I figured it out, instead of filling the unfilled segment part of the donuts with the same background colour, use No Fill.

  • @mirzafarhan7883
    @mirzafarhan7883 Год назад

    Very Confusing

  • @rudiklein
    @rudiklein Год назад

    Jeeezz. No comments, 16 likes?! This is really cool Excel stuff! (And PowerPoint as well). Your channel is stronly under-valued.

  • @YTFC2008
    @YTFC2008 Год назад

    I likert very much, thank you!

  • @sporgussfs7975
    @sporgussfs7975 Год назад

    Cool concept, does it break during timewarp?

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      I don't think anything touching something like this is stable during time warp.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      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.

    • @sporgussfs7975
      @sporgussfs7975 Год назад

      Interesting

  • @pcroft60
    @pcroft60 Год назад

    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.

    • @ChrisArmstrongChemistry
      @ChrisArmstrongChemistry Год назад

      I tend to throw stuff up randomly as it occurs to me, but if anything might be useful, I suppose I could do requests.

  • @gwonchanjasonyoon8087
    @gwonchanjasonyoon8087 Год назад

    goat!!

  • @alimackenzie7742
    @alimackenzie7742 Год назад

    ρяσмσѕм 😚

  • @animeshsarkar7717
    @animeshsarkar7717 Год назад

    Thank you so much for such a important animation...

  • @HRUMS
    @HRUMS Год назад

    thank you very much. I have learned a lot from your presentation.