יום שלישי, 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:


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 😊