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