יום שלישי, 22 בנובמבר 2022

Extracting Country Name from End of String

Extracting Country Name from End of String

Suppose you have a list of addresses, where the country name appears at the end and you need to check whether the name appears in a country table.

This can easily be done, for example, by extracting the last word of the string and using XLOOKUP to verify that this is indeed a valid country name.

 


                   Pic 1: Extract Country Name from End of String

 

But what if the country name consists of two words and not just one?
For example: United States, United Arab Emirates, Ivory Coast, Czech Republic, New Zealand…..?

Then, the previous solution won’t always work.

So, we found a better solution for countries with more than one word in their names.



Pic 2: Country Name consists of one or more words

 

This solution has (at least) three advantages:

1.      It can be applied in any Excel version, not only in Excel 365

2.     As stated above, it can handle countries whose names consist of more than one word

3.    Unlike the first solution, where the country name must be at the end of the string, the second solution allows us to find countries in the middle of the string.

 

 

  



יום שישי, 18 בנובמבר 2022

 

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

 





 


 


יום שבת, 12 בנובמבר 2022

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


יום שני, 24 באוקטובר 2022

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?

 




יום שבת, 15 באוקטובר 2022

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







יום שלישי, 27 בספטמבר 2022

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.


 


יום חמישי, 1 בספטמבר 2022

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


In the cell where you want a "fixed" timestamp (say, A1), type the following formula:


=IF(LEN(A1)>1,A1,NOW())







Pic 2: Write this formula to make the NOW() function immutable

 

Any changes made to the worksheet will not impact the value of A1. The timestamp persists unchanged forever 😊