A new Method to analyze financial data

What I’m going to share with you today is a trick I invented when analyzing financial data: general ledger, balance sheet... The exact term isn’t important as long as you analyze….money.

So, here’s the trick:

Suppose you want to find out the sum of several months: could be a full year, could be 6 months, could be a quarter… you name it.

So, the idea is to have 3 parameters that control the process:

1.       The column to start with

2.       How many consecutive columns do I want to sum up

3.        What line do I want to analyze

                    Example No.1: Summing Cells B7:C7

 In this example, we want to sum up the monthly interest of the first two months of the year: columns B and column C.

 So, the result appears in cell D14 and the formula – in D19.

And the formula is:
=SUM(INDIRECT(P1&P3&":"&CHAR(CODE(P1)+P2-1)&P3))

The INDIRECT Function constitutes the range B7:C7 in a clever way. How?
The first part of the range(“B7”) is created by concatenating the starting column (“B”) [first parameter - P1] with the desired row (“7”) [third parameter - P3].

The second part of the INDIRECT is the more interesting one: we convert the letter “B” (of the first parameter) into its ASCII code: 66, and add the numbers of months to calculate (2) [second parameter - P2], then we subtract one: we need to add only 1 in order to calculate two months.

The number created in this summing process is 67.
Now we convert this number back into an ASCII letter (“C”) and we concatenate it with the row number (“7”) exactly as we did in the first part.

The string created (“B7:C7”) is then summed up, and the result: 18977 is the sum of B7 (8315) and of C7 (10662).

 

Let’s take another example:

                        Example No.2: Summing Cells E5:C5

 

Here we want to sum the second quarter of the year (months: 4,5,6 in columns F,G,H) for row no.5

So, the INDIRECT’s string in this case would be: “E5:G5”, and the sum of these cells amounts to the sum in cell D14: 4164046+5225459+6286873=15676377

 

 

 

Excel 365 - Four Methods to extract only Digits from a String

 Excel 365 - Four Methods to extract only Digits from a String

 

There are, of course, numerous ways to extract only digits from a string of characters.
Today I’m going to show only four and only in Excel 365.

All methods use the SEQUENCE function, an indispensable function when “breaking” a string into its “atoms”.

The third one is using LAMBDA with the brand new REDUCE function.

 

Method 1 – using CONCAT

Method 2 – using TEXTJOIN

Method 3 – REDUCE with a LAMBDA

Method 4 – CONCAT with ISNUMBER

The Ultimate solution to cleaning unwanted characters

 

 

The ultimate Solution to cleaning Data

 

Amazing!
The Ultimate Solution!
No need for Power Query!!!

This problem “pops up” in myriad Excel forums & groups:

How do I “clean” my data?

How do I remove all non-printable characters from a cell?

Excel’s help (the TRIM & CLEAN functions) is very limited.
It cannot eliminate all those "hidden" characters.

The perfect (and dynamic) solution is offered here, in a formula that I’ve developed, as can be seen in the attached pictures.

The formula is quite complex and involves 10(!) Excel functions.

                                    Participating Functions

The solution enables us to choose (by parameters) which characters we want to see in the “clean” result:
For example: digits (ASCII 48-57),
lower case English (ASCII 65-90),
upper case English (ASCII 97-122) etc..
Other subsets of the ASCII character set might change according to locale. For example, in a Hebrew UI the Hebrew alphabet resides in ASCII 224-250.

                             The Parameters Table

 

The parameters table enables you to define the groups of characters (subsets of the whole ASCII character set) that you want in the "clean" cells.

1.    Each category's value starts at the first value of that category. For example, the digits category's is 48, since the digits are ASCII codes: 48-57.

2.    By typing “y” next to the desired subset, in column D, any group of characters: digits, English (lower), English (upper) etc. or any combination of these groups/subsets – is selected for the output result. Thus, you can dynamically control which subset/s will appear in the "clean" cells, without "tampering with" the formula. A category whose switch is "turned off" (i.e., its value is not "y") – will be left outside of the "clean" cells.

Two defined names (in the name manager) refer to the groups and the parameters.

Values (i.e. groups/subsets) refer to cells C3:D11
Switches (i.e. parameters) refer to cells D3:D11 

as can be seen in the picture:

         

                  

                

You are free, of course, to add any subset you desire. For example: Special characters (ASCII 33-47): ! " # $ % & ' ( ) * + , - . /

In my example, cells A27:A33 are the cells to be cleaned.
I chose to display only digits and English letters (both uppercase and lowercase) in the "clean" cells.

Cells B27:B33 hold the “clean” data, which (according to the parameters) have only digits and English letters (both uppercase and lowercase), as explained above.

The somewhat long formula 😊 is displayed here and refers to cell B27 (the "clean" result of cell A27).

=LET(s,MID(A27,SEQUENCE(LEN(A27)),1),IFERROR(CONCAT(FILTER(s,IF(XLOOKUP(CODE(s),Values, Switches,,
-1)="y",1,0))),""))                            

                             

Double-click the right lower corner of cell B27, to "spi the formula to the rest of the range (B28:B33)

And here's the final result:

The cells before (A27:A33) and after (B27:B33) cleaning

 

That’s all.

It is desirable and even recommended to try at home.

Enjoy.

 

TOCOL instead of...FILTER????

 

TOCOL instead of…FILTER???

 

This is a new trick I invented.

Suppose you have 2 lists and you want to extract only the matching values in these lists:

                                                Pic 1: 2 lists to compare

So, the most obvious solution would be using the FILTER function:

Pic 2: Using the FILTER function to display only matching values

 

But I invented a "cool" trick with the TOCOL function.
First we write a simple IF formula, where the FALSE is an invalid value (NA), which Excel interprets as an error:

          Pic 3: the IF function returns an error when there's no match

Now, what's left for us to do is to "wrap" the IF with the TOCOL function, where the second argument to the TOCOL function
is 2: ignore errors.

And the final result:

Pic 4: wrapping the IF function with TOCOL yields the desired solution

Simple, isn't it?

 

Calculations on non-contiguous cells

 

Calculations on non-contiguous cells

 

We are used to do calculations in Excel on an array of cells, where the cells are contiguous.
But what if we want to do some operations on non-adjacent cells?

Take, for example, this scenario:

We want to find the average of all positive numbers in these cells, in the picture:

                             Pic 1: Non-contiguous cells

 

Solution:

We solved the problem in two easy steps:

Step 1:
We define the whole range surrounding the cells as a named range:

The named range is NUM and it refers to the range A1:L4

Pic 2: The Named Range

Step 2:

The formula in D7 will yield the desired result.

=SUMIF(NUM,">0")/SUM(IF(NUM>0,1,0))

This solution is dynamic in the sense that you can, of course, add numbers to the defined range, erase numbers or modify them.
But you don't need to touch the formula!

Pic 3: one formula that doesn't change when the data change

Excel - Solving Math Equations

 

        Excel - Solving Math Equations

 

We usually use Excel for financial analysis, calculations etc.

But did you know that you can solve mathematical equations in Excel?

Take, for example, the following equation:

                         Picture 1: The Equation

Fourth root of (2+x) – fourth root of (2-x) equals 1.

We're going to solve this equation using the GOAL SEEK feature in Excel.

The Goal Seek "lives" in the Data ribbon, Forecast Group, as can be seen in this picture:

                         Picture 2: Goal Seek

But first, let's write the equation in cell D4, and the desired result (1) in D5.

The formula in D4 refers to the variable (x) in D3 which is where the solution to the equation is going to appear.

                    Picture 3: Filling in the 3 parameters

 

After filling in the Goal Seek Parameters (and pressing the OK button), Excel displays the solution (in D3)

 

                         Picture 4: Excel calculates the solution

 

Of course, the Goal Seek feature has many uses in Excel.

The most common one is in loan payment simulations, but you can use it practically whenever you have one parameter/variable impacted by another. You set the "goal" (desired result) in order to see how this goal is reached by/dependent upon another parameter.

This was, of course, a very simple equation.
If I have time, I'll demonstrate solution of some more complex (polynomial) equations in Excel.

Stay tuned.

 

Excel - Fixing the Timestamp

 

Excel - Fixing the Timestamp

 

The Timestamp problem is one of the most annoying problems in Excel.

The =NOW() function returns the current timestamp (date & time).
However, since this function is a volatile function  (recalculated whenever any change is applied to the worksheet) its value will not persist.

But this can be solved if you follow these two simple steps:

Step 1:

Stop Excel from re-calculating whenever a change is made in the worksheet.

This can easily be done if you "tick" the  Enable iterative calculation, as seen in the picture:

 

Excel Options -> Formulas -> Calculation Options – Enable iterative calculation

 

Explanation: by default, Excel does not allow "self-reference" calculations (i.e. when the formula within a cell refers to the cell itself). But this "self-reference" is the crux of the solution, as can be seen in Step 2.

            Pic 1: change the Calculations options for this workbook

 

Step 2

Suppose you want to track the first time you entered data in cell A1.
In the cell where you want a "fixed" timestamp (say, C1), type the following formula:

=IF(LEN(A1)=0,"",IF(C1="",NOW(),C1))

Pic 2: The formula (in C1) will show you the first time you entered data in cell A1  

Result: The timestamp in C1 will not change even if you make changes  to A1.
It will disappear only if you erase the contents of cell A1.
After entering a new value in A1, the timestamp (in C1) will automatically re-appear in C1 and will stay there forever, whether you make changes to A1 or leave it intact.
You can add new data to the sheet, or press F9 - but the timestamp in C1 will not change nor will it disappear - as long as there's some value in cell A1.

😊😊😊