VideoPhoto

28 May, 2021

Pie chart: wheel of VBA colors

If you use Excel VBA and need to set color or get color for cell, shape or chart, you may need the VBA color code list for reference purposes. The ColorIndex offers 56 basic colors and it's hard to remember VB codes for all of them. The following chart can be helpful, if you don't know the color code for your specific task:

In case you'd need to recreate the chart on your own computer,

26 May, 2021

Outliers in normally distributed datasets

I've recently touched the subject of statistical analysis of data using Excel functions. In this post I dive deeper into Excel statistical tools. It's about outliers in data sets, about numbers that distort the state of reality and can lead to unsound findings and conclusions regarding specific areas of knowledge.

There are no strict statistical rules for indisputable ways of identifying outliers; we are dealing with probabilities. Nonetheless, there are guidelines and tests we can utilize to find outlying values, and they can significantly improve our intuition, formally.

Because of the importance of detecting outliers I've prepared Excel workbook providing practical tools (tests) for identifying such deviating/departing values within any set of numerical data. The workbook includes basic guidelines for using some specific statistical tests; I'm showing here its fragment:

24 May, 2021

Simulation of the arithmetic mean and data normality

The world around us is full of uncertainties. In research, in engineering and sciences, uncertainties must be dealt with in a formalized way, using statistics.  We are required to follow procedures for dealing with inevitable variation in routine testing:  in laboratory, in production and construction, in relation to quality control and quality assurance issues.

Excel statistical functions help in solving many practical problems in that area, as well as in simulation and estimation of probabilities of certain outcomes. Here I just like to share with you couple of charts based on simulation I've carried out in Excel regarding the critical role of sampling frequency and the number of tested samples in evaluation of various processes and material properties.

The simulation example shown below is based on assumption of normal distribution of sampling and uses two basic statistical functions:

  • RAND() , which returns evenly distributed random numbers from 0 to 1 (not including 1), and
  • NORM.INV, which returns the inverse of the normal cumulative distribution for the specified arithmetic mean and standard deviation.

Here is the chart illustrating the effect of the number of tests (samples) on the value of Mean. The data have been obtained with the formula =NORM.INV(RAND(),2.60,0.009), where 2.60 is the expected arithmetic Mean and 0.009 is Standard Deviation of the population ('targets'). We can see that variability of the running Mean is very high up to about 15 tests. Its reliability increases with number of samples and reaches good stability starting at around 50 tests (samples). At the same time the spread of data widens up to about three standard deviations, as can be expected in any normal distribution.

23 May, 2021

Pick your lucky Lotto numbers

If you play Lotto, how do you pick your lucky numbers?

Everyone has their own method. Some people like to analyze past draws and study the statistics to select potential winning patterns. Others rely on totally random ways. My own experience shows that statistics helps a little bit with winnings at the low end only. That’s it.

No method can guarantee a big win. Lotteries are completely random. To check out my 'independent' serendipity - years ago - I decided to create my own Lotto number picker coded with VBA. It’s one of my modest and oldest Excel creations. Here's its face in a worksheet:

Simple and functional. It just picks six lucky numbers for you to play in a lottery like Lotto649 or something close to it. It can be modified quite easily for other lottery games based on random selections. You can get your very own quick picks on your own computer.

20 May, 2021

Printing multiple workbook ranges on one page

Sometimes we may need to print couple of areas located in separate ranges of our worksheet or on different worksheets in our workbook. The reason? This way we could simply save couple of paper sheets. Also, such combining of separate ranges on one page might be useful, or even necessary, for comparison / presentation purposes, etc.

So, how could we do that?

The easiest way, I'm using, is to employ the Excel Camera tool. The tool might not be available by default on your Quick Access Excel toolbar (top-left corner). If so, you can add it there by choosing Toolbar>Customize>More Commands...  Scroll through the list of Commands to find Camera and drag it to the toolbar. From now on it'll be always ready for you to use.

 
Here's how to use it, e.g. for printing:

19 May, 2021

Creating Surface Charts 3D

Surface charts in Excel deserve special attention. They allow to see your data in three dimensions and can be particularly useful in engineering applications (design and control of processes) as well as in the art of drawing and painting.

Typical surface chart shows how the dependent variable (z) behaves as a function of two independent variables (x & y). In other words, the chart illustrates outcome of any nonlinear function involving two variables. E.g. we can plot speed of some technological process (reaction) as a function of pressure and temperature.

In this post I'll show how you can prepare a template for creating surface charts based on equations including variety of math functions (algebraic, transcendental, periodic). Such template can be very useful in all kinds of design, as it allows you to visualize plenty of solutions in minutes.

Let's start with just one example of surface chart I've created. This is 3D plot of equation shown in the title of this chart:

 

How can you create similar charts based on your own equations?

17 May, 2021

Presentations in Excel

If most of your work, you do for presentations, comes from Excel, there is no reason to use the PowerPoint instead of Excel itself for preparing your demonstration.

In fact, all your charts, tables, forms, textual info, background graphics, and even sounds, can be quite easily presented and put in order in Excel worksheets.  All the hassle with extra work of copying and pasting into PowerPoint could not be necessary.

Let's say, you've carried out your data analysis and prepared workbook with 30 perfect  worksheets ('slides') for your quarterly presentation. How would you proceed to get ready for reporting your work? Switching to PowerPoint? Not necessarily.

If you'd decide to stay with Excel, the following steps could probably be more efficient way to go. 

First, put all your presentable worksheets in consecutive order in which they'll be presented.

Add a blank worksheet dedicated to a title page. Excel has all the tools needed to create an attractive design. Such a worksheet could show just the subject of your presentation or might be a bit more elaborated, as e.g. in this figure:


Similarly, you can add another worksheet for displaying "The end" page, if appropriate.