VideoPhoto

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:

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:

2*x^3-15*x^2-195*x+990=0