excel.kingofmath.comExcel Workbook · How? What? Why?

Excel Workbook How? What? Why? Home About Subscribe to feed Fun with Bar Charts – Part 2 – Drawing Pictures August 26, 2015 in -Advanced , Charts , Formatting by Peter | No comments Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract. As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe?? What came immediately to mind is the fundraiser tracker. You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money: I wanted to create a visualization like that, in Excel, next to my family’s finances. Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable. How do I do it? Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts. Wanna draw a circle with an absolutely rectangular bar chart? Let’s do it visually: Step 1 – Create a stacked bar chart. The number of stacks that you need depends… and trust me for now that you need 4 for a circle: Step 2 – Make sure you have enough bars. 100 is always a good starting point in my opinion. Step 3 – Set the gap between bars to 0%. Now this chart looks like some European country’s flag. Still quite rectangular: Step 4 – Adjust the height of these 100 bars so they look like this. Note how some stacks are much taller than others: Step 5 – Resize your chart into a square. Clean up the colors and update the axes ranges, and… Ta-da! Here’s the circle! Don’t like how pixelated it is? Add more bars! The key here is to think of a picture as a rectangle with w-by-h pixels. Then you can replicate it with w stacked bars each having a maximum height of h. How many stacks you need depends on how complicated your graph is. Is it possible in theory to draw a Mickey Mouse? Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors. You’d need to either set up formulas or a lookup table for the height of these stacks on each bar. For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0. Back to my original goal of drawing a target tracking graphic. I decided to go with a house complete with a chimney, even though our home looks nothing like it. It involves a handful of vertical, horizontal, and sloped lines: The shape of my house, including where the chimney is, is entirely parameter-ized. So by updating a few cells I can get to alternate versions like these: Lastly, I needed to fill it in with the progress tracker, as follows. Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value: Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well: This way, I’ve got a bar-based graphics that updates automatically. The only thing left to do is actually paying off that mortgage! Fun with Bar Charts – Part 1 – Waterfall Charts August 24, 2015 in -Intermediate , Charts , Formatting by Peter | 1 comment Only because my soon-to-be-former coworker Monica asked about this topic, I decided to dust off this blog (finding that login was no easy task!). It’s hard to believe but the date stamp on the most recent post was… six years ago! It’s not that I’ve forgotten about this blog, but I’m borderline at a point in life where you can’t teach an old dog new tricks. And when I don’t learn new tricks, it’s hard to blog about them. Well, I did learn one thing more recently than 2009: bar charts. Remember when we all first learned about these fundamental expressions of statistics, like, in fourth grade? Drawing the axes with a pencil and measuring out the height of each bar with a ruler was among the most painful part of any math class that I can remember… and yet, this two-post series is about those. A steroids-injected version of those boring bar charts. In its first part, we will examine how to create waterfall charts with Excel’s native capabilities. A waterfall chart is one where floating bars are used to illustrate the progression of a number. For example, I used one in my recent guest blog entry on Hungry for Points , to outline how my points & miles balance changed between two points in time: Added some, subtracted some, and a new balance! On the surface, it seems impossible to draw floating bars with Excel’s charting tool. That may be true, but the trick is to craft an optical illusion with white bars. The same idea can be applied to both overlapping clustered bars and stacked bars. Let’s see how the former works: Step 1 – Determine the step-wise cumulative value, and the increment/decrement amount: Step 2 – Create a clustered bar chart where each step has two bars: for increments, bar 1 should be the previous cumulative amount and bar 2 should be the new cumulative amount; for decrements, it’s the opposite. For the first and last steps representing the balances, bar 1 should be 0: Bar 1” is orange here. Step 3 – Make sure bar 1 is in front and set the overlap to 100%: Step 4 – Fill bar 1 white, or whatever your background color is: Step 5 – Color the individual bars: Step 6 – Add data labels to explain your chart. These can easily be formulaic and linked to your numbers: Voila! This may look exotic, but it really isn’t! Stay tuned for the next article in this series where we take this technique to the next level! Quick Peer Review September 11, 2009 in -Beginner , Array Sums by Peter | Permalink For those of you in the business of peer reviewing other people’s data entries, the ability to minimize tedious checks is essential. Today we’ll look at how =SUM(ABS(…)) is a convenient tool to reduce a simple 3-step process to a simpler 1-step process. Your intern has just copied a large table of numbers from Worksheet1 to Worksheet2, and you want to make sure nothing went wrong in the process. In best practice, you’d want to check not only the numbers but also the relative position of the numbers… did the intern switch some rows or columns around? A good system of lookup formulas should be employed to ensure the integrity of this data transfer. However, for now let’s just focus on the most basic case where all we care is that the two tables match identically. It’s a huge table of 100 rows and 20 columns, so you really don’t want to look at each number with your bare eyes. A typical way to check is to set up a third table of the same dimentions, and subtract the corresponding cells in the two tables: (Worksheet3!A1) =Worksheet1!A1-Worksheet2!A1 (Worksheet3!A2) =Worksheet1!A2-Worksheet2!A2 …. After that, a quick glance for nonzero values will help you find places where discrepancies exist. This is not difficult or time consuming by any means, but you still have to (1) type in a formula, (2) copy formula to the correct range, and (3) check for nonzero values. Instead, try this array formula : {=SUM(ABS(Worksheet1!A1:T100-Worksheet2!A1:T100))} (Don’t forget to press CTRL+ALT+Enter ) This formula does an element-wise subtraction of the second table from the first table, and totals the absolute differences into a single number. If the result of this formula is nonzero, then we’ve got a discrepancy problem worth further investigation – otherwise the job is done! The ABS() function here serves an important role at making sure positive discrepancies don’t happen to offset negative discrepancies. Automatic Goal Seek January 13, 2009 in -Advanced , Visual Basic by Peter | 24 comments This article demonstrates a simple VBA method to utilize the Goal Seek tool automatically, similar to a native Excel formula. Goal Seek is an Excel built-in tool that uses numeric approximation methods to back solve” for an input value to a system of formulas when given a desired output value. If you are not familiar...