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 last time you made a change in cell C1.
In the cell where you want a "fixed" timestamp (say, A1), type the following formula:

=IF(LEN(C1),NOW(),NOW())

Pic 2: Write this formula (in cell A1) to show you the last time cell C1 was edited 

Result: The timestamp in A1 will change only if you make changes  to C1. Do not "touch" any other cell and do not press the F9 key.   

Whenever you change the value of C1 (even if you press the Delete key within that cell), the timestamp in A1 will update.
😊😊😊