Excel Workbook that Solves an Equation When Any Independent Variable Changed

You get the idea, you have something complicated, like this :

And you want to solve for σ. But, you want to be able to update a, b, G, and lambda on the fly and see the new σ that makes LHS = RHS. How? 😊 Obviously you want one cell to capture LHS - RHS so you can solve for the value that sends this cell to 0.

Start off getting stuff to work using Goal Seek (Data > What if Analysis > Goal Seek.

Now, the cool part is getting that to run on demand through code. What can you do? Use the Macro Recorder obviously. Do that and you have this in your pocket :

Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")

Now, the cool part is getting this to run when the cells in C8-C11 are changed. How? 

In Excel, do ALT-F11 to open the VBA Editor and then, in the Project Explorer, double click the name of the sheet under Microsoft Excel Objects. You now get a new window called filename.xlsm - SheetNamed (Code). In the dropdown on the left, choose "Worksheet" and now, on the right, choose  "Change". This will give you this starting code :

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

All you have to do is :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = Intersect(Target, Range("C8:C11"))

    If Not rng Is Nothing Then

' put your magic code here  - what magic? The stuff that copies the numerical value you calculate use a formula for the initial guess into a cell that you tell Goal Seek to vary of course 😊

Put it all together: 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = Intersect(Target, Range("C8:C11"))

    If Not rng Is Nothing Then

    '

    ' solve Macro

    ' run goal seek

    '

        Dim sourceCell As Range

        Dim targetCell As Range

        

        ' Set the source and target cells

        Set sourceCell = Range("D12")

        Set targetCell = Range("C12")

        

        ' Copy the numeric value from the source cell to the target cell

        targetCell.Value = Val(sourceCell.Value)

    

        Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")

    End If

End Sub

And you're done :) Enjoy

Kelly, Goldmeier and Bach - An Eternal Golden Braid

Whatever - needs an unconventional title don't it? Here's the challenge :

Your data looks like this :

Name	N	S	E	W
Alpha	700	800	900	500
Beta	2	3	5	6
Gamma	111	445	333	212
Delta	78	11	99	2

And, you want to be able to (easily) plot the bars for any ONE of the players AND, also make the largest bar STAND OUT. (Excel without VBA, overlay plots show bars from data with max bar highlighted)

What? If you're plotting the bars for Alpha, you don't want to see just this :

But, you want to see this! :

And, you want to be able to (easily) change between the players. There's a few things I didn't know you could do. The easy one is - if you have a chart and click on the title, you get to put in a formula! You just have to point it to the Data field that the user is going to use to "Choose the Player"!

First, the on-demand chart - that, is the plain vanilla (no max highlighting) that gets you a player's performance on demand (yes, I know it's salesman, but player sounds cooler, right?)

Easy - 

In  cell, put your cursor, then, go to the Data menu and then click on Data Validation (if you don't even see the Data menu, which I highly doubt. I can understand if you don't see the Developer menu)

In the resulting "Data Validation" dialog, set "Allow:" to List and for "Source:", click in the field and then go to the worksheet and click and drag through the cells containing the names. You can do this much with a pic right? :)

Now comes the fun - how do you get the numbers corresponding to the player you've chosen? (Using the drop-down)

Pure gold :

Enter (in one cell, and then you can drag ) :

=VLOOKUP(                             (and now notice the tooltip)

Now, when you're being prompted for "lookup_value"

you just click on the drop-down cell (where the user chooses the Player's name) you recently created with Data Validation, et al.

In my case, I get B19 when I do that. Is that what I want? Sort of. I want $B$19. The recipe? After clicking the reference, you hit F4. Did you know that? Ain't that cool?

Type the comma, and now the fun - it's asking you for the "table_array". Here, you select the entire table, but not the headers. So, you click and drag starting on Alpha and finishing on the "2" at the bottom right.  Yes, if you're wondering, that's not the only start and finish you can take :) But don't be perverse now :)

And then? Magic! Type the comma and then (without the quotes, duh) : "{2,3,4,5}"

The reasoning? If you noticed the tool-tip this time, it's asking you for column numbers. Well, the names are in Column 1, obviously :). But, why the curly braces and the array?

That comes in handy when you do the drag to fill. For now, just finish up with the final parenthesis, the ")" and see if Excel is happy (i.e., it has dutifully displayed the appropriate value from the "N" column for this Player. Yes? Good.

Your cell that you just finished will look something like this if you care for the formula :

=VLOOKUP($B$19,Table1,{2,3,4,5})

If you didn't format the data to be a table, then it's probably like this :

=VLOOKUP( $B$19, B26:F29, {2,3,4,5})

Now, you put your mouse pointer on the bottom right corner of this cell and drag over the next three cells to the right. You'll see four cells highlighted, and looking something like this

That's cause you need to give Excel more info - i.e., define this array. So, if these four aren't already selected, select them, then go to the formula field, and put your cursor in it and hit

CTRL+SHIFT+ENTER

This defines the array. You should now see them magically show the four values corresponding to this player. If you choose a different player with the drop-down, you should see the values update.

From here, on, the first chart is easy. You select the four cells, and the Insert a chart :

Just use this one for now :)

And you get your chart. You'll notice the X-axis don't exactly have the N,S,E,W :) What do you do?

You'll see the Chart Tools menu being active (look at your title bar :) Click on Design, and now you'll see button for "Select Data". In the resulting "Select Data Source" dialog, you click on the "Edit" button (how do you know it's a button? Don't ask me) and then, you get a new dialog called Axis Labels. You can now select the N, S, E, W cells and you're through. Phew :)

Okay, now to make this chart magical - that is, get the max bar to stand out!

Easy, all you do is make another four cells, (you get the idea, you're making another chart that you're going to paste over this one!) where only one has a number other than zero :) You dig?

Easy - you just enter

=C19*(C19=MAX($C$19:$F$19))

And drag this. See the logic? Unfortunately, hackers would prefer the "=" were actually "==", but M$ disagrees :(

"Cell-Value times 1 only-if-cell-value-is-max-of-these-four-cells else 0 "

Simple? The Boolean takes care of the 1 else 0 part.

Now, Just select these four cells and do a CTRL-C and then, click on your existing chart, and do a CTRL-V. Look at im! You have to give someone credit for at least making this one somewhat intuitive :)

Yup, we're not quite there. (If your eyes are open, you know you already have enough info to fix the "Chart Title" :) Go to it! ) That's where this guy helps out :

https://www.extendoffice.com/documents/excel/4840-excel-bar-chart-overlap-gap-width.html

How do you get the red to dominate? Easy (if you know how :) - like with all things M$) :

Click on one of the bars, then right click and choose "Change Series Chart Type" :

Then, all you have to do is check the "Secondary Axis" checkbox for Series 2. And you're done!

Excel Keyboard Shortcuts - Why Is This So Complicated? Thank You M$!

Nuts! The shortcut defined in a particular workbook should be active when that workbook is active!!!

A vain attempt by Allen Wyatt : https://excel.tips.net/T002872_One_Shortcut_for_Two_Macros.html

Coming soon...

Thank You Charlie Nuttelman : Excel VBA Stuff

So much I didn't know about - man - you don't get to be a trillion $ company with a cash cow unless it has some serious bells and w.

Relative referencing while recording macros - this can make you more productive building a macro to automate a recurring task - check it out!
The Locals window - watch your productivity zoom, already!

The thing that didn't do much for me was the Object Browser - a clear tutorial might help there.. (BTW, you might find his start in Lesson 14 adorable. You'll see how you easily put together something like

Private Sub Workbook_WindowResize(ByVal Wn As Window)
MsgBox ("You resized me!")
End Sub

which is very cute :) )

Other things I must thank the Nutty Professor for :

1. Exit For, Exit Sub, Exit Do -- sometimes ease trumps elegance
2. Option Base 1 -- when you want to be a lay person and count starting from 1 :)
3. ReDim Preserve -- you declare a variable and now, you know how big your array is going to be (or you need to resize it - make it one bigger, and, if you don't say Preserve, you're going to reset all elements to 0... So..
4. Relative Referencing while recording macros - so cool :)
5. The Locals Window! What a lifesaver
6. Declare (Dim) at the Module level, and you can access in all modules
7. Optional arguments to Functions and Static Variables
8. All Dim'd variables start out with a value of 0
9. Debugging : run to cursor : CTRL-F8
10. If you want your code to automatically enter debug mode upon a certain condition being met : Debug.Assert <condition>
11. If you're in the VB Editor, ALT-F11 takes you back to the Workbook - you'd think I'd know that one :)
12. XLAM - Excel Macro Add in - how to Add-in/make a function available
13. How to run a VBA routine from another file
14. The Object Browser - like I said the YT tutorial by ExcelVBAHelp is better
15. You can do .Range of a Range, because a Range is now its own Spreadsheet!
16. Application.ScreenUpdating = False -- to get stuff to finish faster
17. Goal Seek, Solver, Bisection Method, Circular Calculation enabling
18. The power of Dim'ing as Variant - for an array - assign selection without for loops..

Where Is Your PERSONAL.XLSB (Personal Macro Workbook) Located?

For a change, Google didn't just spit out the answer right away..

https://stackoverflow.com/questions/44183185/where-is-your-personal-macro-workbook-located

What you do :

1. Make it visible (View Tab > Unhide > PERSONAL)
2. Press F12

And you get to see.. In my case :

C:\Users\<name>\AppData\Local\Packages\Microsoft.Office.Desktop_weirdbuchofchards\LocalCache\Roaming\Microsoft\Excel\XLSTART

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

Freres Vs Rothschild. Who is the Deadliest Warrior?

Excel macro : How to swap two rows? Thank you Mice Rickson

See the previous post on how to do macros and bind them to hotkeys (yes, you only have a couple of keys left since M$ only allows CTRL+ …. idiots of Redmond!!)

https://www.ozgrid.com/forum/forum/help-forums/excel-general/76910-swap-switch-2-rows

Sub MikeRickson_Swap()
    Dim tempRRay As Variant
    With Selection
           With Range(.Areas(1), .Areas(.Areas.Count)).EntireRow
                tempRRay = .Rows(1).Value
                .Rows(1).Value = .Rows(.Rows.Count).Value
                .Rows(.Rows.Count).Value = tempRRay
           End With
    End With
End Sub

M$ Excel : How to Capture the Max of Only the Displayed Values of a Column in a Table

MAX is not your friend. SUBTOTAL is. How should you know that? Don't ask me.

So, you have your beautiful data and, you've done the Format as Table trick.

Now, you can quickly do filtering on it - to see how good/bad things are under different conditions.

Thing is, can you have a cell somewhere up top that shows you the MAX of your table, and uses ONLY THE DISPLAYED VALUES. Else, what's the point. Right?

Thanks to these (https://exceljet.net/excel-functions/excel-subtotal-function) guys, here's what you do :

You start typing : =subtotal

You'll get prompted by Excel with SUBTOTAL. Hit Tab key to select that and now, you have to choose the function. If you refer above page, you'll find that the version of the function whose number has "10" in front (i.e., 101 vs 1) ignores hidden values. So, for MAX, it's 104. Thankfully, Excel gives you a gorgeous drop-down menu that makes life super easy.

So, if you've named your table (good idea:), you'll end up with something like

=SUBTOTAL( 104, myTable[@col_name])

And it'll work great!!

M$ Excel Apply Data Validation Using Custom Formula

If you wanted a whole number between known limits, easy... What if it has a fractional part? Why can't the *ds at M$ provide for this case as well?

How are you supposed to know you need an "=" at the beginning? Good q! *ds!

The Uselessness of Microsoft Excel

How can such a simple feature be missing from such a key product?

I want to search for "form" and not find "information". That is, I want to "match entire word".

Why? Why? Why?

Please, developer, developer, developer, developer, developer,.... *ds!

Handy Excel Hacks

https://excelwithbusiness.com/wp-content/uploads/2017/10/100-Excel-Tips_EwB.pdf

Paste Special : ALT E S V
Also, Transpose can be very handy at times.
Add Multiple Rows
F4 for Absolute Reference - did you know that pressing F4 repeatedly will toggle through the various combos?

A treasure trove!

How to Paste Image in Clipboard into (at) a Cell in Excel

Well "into" means "at" a particular cell.

Try it with just on Excel file open. You'll see that Excel always dumps the picture right at the top. What a pain? Thanks Satya for another dose of crappiness. Where would Mac be without something really crappy to compare to?

Anyway, the workaround is

Have a scratchpad Excel doc open.
Paste the image from clipboard into *that* scratchpad.
Right-click on the image in the scratchpad and choose "Copy"
Now, go back to *your* working Excel doc and select the cell you want to paste at.
Do Ctrl-V

Excel Pivot Table Torture

Python pandas can do it, but, of course, M$ Excel can't. When will Redmond learn from open s?

You want values that are plain text, not numbers. What can you do?

The first order of business is to create an accompanying column of numbers - a unique number for each text item. That is, if your column has

A
A
B
D
E
F
A
C

Then, you want

A 0
A 0
B 1
D 2
E 3
F 4
A 0
C 5

perl -n -e 'BEGIN{%table=(); $count=0} chomp; unless( defined $table{$_} ){   $table{$_} = $count++; } print "$_," and print $table{$_} and print "\n";' 

(Meaning, copy that one column into a text editor - aka NEdit - in Cygwin, and then pipe through the above perl script :) (See why you need unix? :)

Then, you run into the other roadblock. You add this column to your existing Table, and the Pivot Tables simply don't see it. WT*? Luckily, "how to refresh pivot table options" with Google delivers - and works..

Always Valid Excel Time of Last Modification in a Cell

This one is crap : https://www.extendoffice.com/documents/excel/954-excel-created-last-modified-time.html

Look elsewhere..

It's simple :

=NOW()

And then, select that same cell, and Format > Format Cells and click on the one that says m/d/yyyy h:mm and then, when you do that, notice that the Type: field has now gotten populated. Click there and change to m/d/yy h:mm unless you expect to live till 9999 AD :)

And of course, it's not perfect :) You guessed it :) If you undo your change, then the cell doesn't retract :)

Who can be a hero and figure this out?