Building a Live Calculator (Complicated) in Excel

That is, say you have a complex equation that tells you how Y depends on x - a closed for expression basically.

But, you want to tabulate x for Y - that is, someone gives you a table of Y values and now, you want to fill in the x values - remember, you only have the equation for going the other way!

Thanks to Charlie Nuttelman - the nutty professor, we have a solution..

Here's what you do...

Get up to speed on the bisection method for problem solving. That is, you want to know what x gives f(x) = Target. So, cast the problem as f(x) - Target = 0 and now find the x that zeroes the LHS. If you're guaranteed only one root in a particular range, you can use bisection. I know... read up!
Implement the bisection! That is the first row is
min , f(min)-Tgt , (min+max)/2 , f(mid)-Tgt, max , f(max)-Tgt

2nd row is (now using g for f - Tgt to save my fingers :)
min = IF( g(min_prev_row)*g(mid_prev_row) < 0, min_prev_row, mid_prev_row ), g(min), mid=…
you get the idea :) I hope :)

Once you have the 2nd row done, you just drag that guy down to 20 rows (at which point your guaranteed to be within (I think) 0.1% of the final solution)

NOTE that the Tgt must exist in cell of the sheet so you can refer to it later.. Capture also the result (mid from row 20 of your drag) into another cell..

Then, you set up a table with

Tgt_val , =<cell where you captured the "answer" - see two lines above >
5
10
15
20
…
valN
valN+1
…

Yes, it's not pretty that one column has something that looks like a header and the other has a reference to another cell - what can I say - M$ is not Apple.

Now, select all of this - from the Tgt_val cell to the bottom right cell of these two columns and then go to  Data > What if Analysis > Data Table

For the Column Input Cell, you give it the cell where Tgt exists - which your bisection iteration table uses..

And that's it! Thank you Mr. Jerry Lewis. You'll impress a few folks with this one!

Microsoft Is (not) Evil

How the hell does a company get to this size having overlooked such a basic feature for 30 years? Damn! Even Cadence has it.

Undo!!

Yes, for manual changes, you can undo. Run a macro and,... you're screwed.

Anyhow, to make up for it, they do let you create a macro to toggle the highlight colour of a cell :

Sub toggle_hilite()
    With Selection.Interior
        If .Color = 16777215 Then ' 25 bits all 1
            .Color = 65535
        Else
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End If
     End With
End Sub