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.

😊😊😊