Showing posts with label table. Show all posts
Showing posts with label table. Show all posts

29 January, 2022

Unique Advanced Filtering

Advanced filtering in Excel is in fact nothing new in recent years, but it is a very useful feature, worth revisiting and reminding its role in data analysis and presentation. In the following example I demonstrate how you can filter the selected data (a list in this case) to show the unique names/IDs, based on just one simple criteria. Not only that; at the same time you can get an additional information - number of times each of the unique names/IDs appears in the list. Here's the setup and the result:

17 January, 2022

Calculating an AVERAGE between any two dates

You've created a table which contains a column with dates. Now you need to do some calculations on numeric data included between some two selected dates. E.g., you want to calculate average of some 'Output' numbers falling within one month, December 2021, like in this table:

If your table is sorted by the 'Date' column, the simplest way to get your average 'Output' is as follows:

11 January, 2022

Excel Chart and COVID-19

In most cases a graphical display let us to see what otherwise we wouldn't be able to perceive in the structure of a table. The chart and fragment of the table presented below confirm that. The chart summarizes clearly the progress and number of recorded human deaths - per one million people - caused by Covid-19 within one-year span, in populations of the five European countries. It's easy to notice, the following:

UK:  1.85 deaths per 1000 people within the year, and further
IT:    1.84 deaths
FR:  1.48 deaths
PL:   1.45 deaths
DE:  0.92 deaths

Plateau of deaths caused by Covid-19 is visible in several summer months, followed then by quite sharp rise during wintery weather. 

The table is based on data published on: 


04 January, 2022

Template for Basic Data Summary/Analysis

If you have collected and organized some data in table(s) or list(s), the first thing you might need could be to get quick general analysis/evaluation of that data - before going any further with some math, statistical or graphic analysis.

The following example provides easy solution, kind of a template, for that preliminary summary and basic analysis.

Here's just illustrative table of random data. Let's say you want to know "everything" about this set of values.

22 November, 2021

SUMMING UP selectively based on references - using ARRAY FORMULAS

Some business sells some products, e.g. vegetables, to various countries/ merchants. Records of sales are kept in Excel table, like in this simple example:

02 November, 2021

VLOOKUP without limits (case-sensitive): using IF+ INDIRECT+MATCH+EXACT+INDEX functions

Here is a small data table, as an example of Excel table, created for the purpose of this exercise:

What if you are working with a real very big table, let's say 20 columns and 20000 rows, and want to retrieve quickly some specific information from such table, from any row and any column. The following table provides some examples of lookups - based on my small table - you'd might want to do:

26 October, 2021

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

30 September, 2021

How to Navigate in Excel Worksheet

Moving around a worksheet made easy. Just fix in memory several intuitive  shortcuts/clicks, as illustrated in this sketch, and you can move swiftly to any place in your even very large data range, and beyond.

Current data range area (a table) is shaded in light green colour here.

In a list format these shortcuts can be summarized as follows:

  • Arrow keys (right,left,up,down)  - move by one Cell
  • ALT+Pg Up / Pg Down               - move by one screen to the Left/Right
  • CTRL+Arrow keys                     - go to the edge of data range
  • CTRL+End key                           - go to the last cell boundary (Row/Column)
  • CTRL+Home key                        - go to cell A1
  • Home key                                    - go to the beginning of a Row
  • Pg Up / Pg Down key                 - move by one screen Up/Down
  • Double-click on any edge of active cell - go to any edge of current data range

08 September, 2021

Goal Seek: Solving cubic equations

Let's say you you know the desired result of some formula, but you need to find possible input value(s) to achieve that result. You may even know the approximate values of those parameters. However, it's probably nothing more than guessing, and you need exact values.

In such cases it's better to use Excel's Goal Seek tool which helps to arrive at exact solutions very efficiently.

I'm providing here an example of using the Goal Seek. The example shows how to setup your worksheet for solving a cubic equation, such as Ac*x^3+Bc*x^2+Cc*x+Dc=0, where I renamed the coefficients a,b,c and d to Ac,Bc,Cc and Dc.

In this case we want to find the roots for this specific equation:


29 August, 2021

Data Analysis: UPDATE on CoVid-19 Coronavirus Pandemic

Three months later... This is the update of my analysis posted in May this year.

The following is based on information provided by Coronavirus update as of 29 August 2021.

The table below provides list of countries with the highest number (so far) of lives lost per 1000 people living in those countries.  On the right side it shows also countries with a low number (but not the lowest) of deaths attributed to the CoVid-19.

It looks like there is a striking increase of deaths among Peruvians, and a relatively low number of deaths per 1000 residents in Iraq, among others.

The following chart illustrates just the left-hand side of the data table...