Pyro's Excel Goodies

[Pyrotex]

HELLO. :)

 

The is Pyrotex the Quintessential -- Spreadsheet Samurai.

 

I have been asked by Freeztar to post all my Excell spreadsheet goodies in one place, and this is that place. As I said to him in a private message:

 

BWAHAHAHAHA! Da Pyro, he got lotsa lotsa goodies, oh my yes!!!

BWAHAHAHAHA! Da Pyro, he dream in Excel VBS, oh my yes he do!!!

BWAHAHAHAHA! Da Pyro hav de spreadsheets that unfold demselves bigger dan you can tack to wall, and take you to da mat'ematical soul of da Cosmos!!

...or words to that affect. {ahem}

 

For starters, I give you the "Living Bit Quilt".

 

This one relies on "Conditional Formatting" for three of the colors, yellow being the static background color. The values in the cells are integers, being the left-most decimal integer in the "distance" of that cell from the upper-left corner of the quilt (as determined by ordinary Pythagorean formula). For example, if the distance is 8.47239... then the cell contains a "4". The "X" and "Y" values for each cell are hidden in the black stripes at the top and along the left edge.

 

Hold down the keyboard shortcuts, and watch the quilts morph in front of your eyes. Animation!!! :) Enjoy.

 

= = = = = = = =

 

ATTENTION! Brief and easy to read Excel Guides to the Basics of 1. Formulas and Equations -- and 2. Macros (VBA) are available for your edification at Posts #42 and #43 in this thread.

[Pyrotex]

This one is the Navaho Rug Generator.

 

Not all of its rug patterns are good---but some of them are spectacular.

 

This one operates with a VBA macro that first generates a "stamp-pad" pattern, and then "stamps" that pattern in a linear sequence in the upper-left quadrant of the rug. The other quadrants are obtained by symmetry.

 

This is an excellent example of how the VBA code can interact with formulas in cells of the spreadsheet. The formulas do calculations that are tricky or time-consuming to do in VBA. The VBA then accesses the calculations much like "variables" and performs the processing that makes the rug.

 

BTW, you can add or delete entire rows and columns out of the middle of the rug, and the VBA still works! Rugs with different aspect ratios make subtly different patterns of rugs.

[freeztar]

Cool! :)

 

Bring 'em on! :)

[Pyrotex]

This one contains no VBA, but does some rather complicated text manipulation with cell formulas. It is called, Scramble.

 

What it does is take any seven-letter word, and generates random rearrangements of those seven letters. Not as easy as you may think.

[Pyrotex]

This was an actual working project management spreadsheet,

called Waterfall Hours.

 

A "waterfall" chart is one that lists tasks down the left edge, and a calendar across the top. In the body of the chart, there are horizontal bars indicating when each task is being worked. Since the tasks are generally in chronological order, the bars tend to join up in a series of "waterfalls" and thus the name.

 

This one has a true Work Breakdown Structure (WBS) on the left, itemizing the tasks within each WBS element. The input values the user inserts are the days of delay of each task from the beginning of its WBS element -- or delays of each element from its parent element. AND, the number of days required to do that task, AND the number of Equivalent People (EP) required to perform that task.

 

The cell formulas automatically draw the waterfall chart, and on the following page, calculate the total labor loading and give you a chart for that.

 

BTW, check out how I created the "calendar" itself along the top of the first page. That's worth the price of the whole spreadsheet.

[Pyrotex]

Here is a pair of spreadsheets that may be useful for you engineering types. The first takes a sparse matrix (a matrix where most of the cells are empty) and compresses that into a much smaller table. The second takes the compressed table and regenerates the sparse matrix. Together, these are the Sparse Matrix Utilities.

 

BTW, building the compressed table requires a VBA macro.

Regenerating the sparse matrix does not. :)

[Pyrotex]

This one is an Ocean Simulator.

 

WTF, over? It simulates an aquarium, if you will, containing plants, fish and sharks. The shark eat the fish, the fish eat the plants. All three procreate. The shark and the fish only procreate if they eat enough, else they starve and die. The entire life history of a shark or fish is encoded into an ordinary real number. :)

 

This is VBA macro-intensive, as you might guess. As the critters move around in the Toroidal ocean (top connects to bottom, left connects to right), eating and being eaten, another macro is meticulously counting each kind of critter in each generation and plotting the results.

 

See the boom and bust cycles of nature in your very own computer!!!

[Pyrotex]

Remember that Spirograph toy you got for Christmas? You pick out some gears, insert them in the base, stick in a colored pen and turn the crank. Psychadelic, dude! :)

 

Well, half a century after I got my Spirograph, I simulated it in my computer. Us wizards can do that, you know. And here it is for your amusement.

 

WARNING--this spreadsheet is a major addictive time waster!

 

The really fun part of this program is that it is ANIMATED. :)

There are four parameters and incrementing them

is controlled by the keyboard keys: A, S, D, F

Decrementing them is controlled by Z, X, C, V.

For your convenience, these are all situated in the extreme left corner of your keyboard. Just hold down Control-Shift- and one of those letters. Sit back, watch the screen, take a puff on your bong (just joking!) and watch half the day disappear before you know it.

 

No VBA required -- all done with math and charting.

[Pyrotex]

Had a little too much to drink? Well... let's go on a Drunkard's Walk.

 

This is another VBA macro acting on an arbitrary-sized toroidal rectangular area. It literally simulates a drunkard's walk--each step can be randomly up, down, left, right. Of course, the edges of the rectangle are connected, up to down, and left to right.

 

Here's the juicy part. As each cell is visited, its value is incremented, and the cell is colored accordingly from a long spectrum of colors. The patterns created look like topographic maps of Pacific islands.

 

There are some tricks in the borders. Watch for them.

 

So, just open it up and click on the "Paint 1" button.

[Pyrotex]

This is a strange one. It is an Egg. And it hatches.

 

(Danger, Will Robinson! Danger! Danger!) :singer:

 

This is a Work Breakdown Structure, Automatic Cost Manager, spreadsheet CONSTRUCTOR. :eek:

 

Read the notes very carefully before triggering the "hatch" process, because when you do, it will take about ten (10) minutes (yes, that's U.S. standard minutes) for the five original pages to become about 15. About 15 LARGE pages. This is the most VBA code I have ever written in one spreadsheet.

 

The "egg" information is contained within a detailed WBS which is carefully numbered. You have to insert a lot of customization parameters, for example, telling it whether the WBS is for 1 or 2 companies, their names and abbreviations. You have to select what labor codes you will be using. You can even select the colors you want to use. Or, what the hell -- just use the default values already there! :D

 

And then you pull the trigger... and stand back... :)

 

It will help if you are a Project Manager--then you will understand (and appreciate, I hope) what you will finally see.

[alexander]

bow to the excel master....

 

i'll test them in open office in the morning tomorrow, gotta run to class, peace :)

[Symbology]

What was the inspiration / reason for the Ocean simulator?

 

In my first run, my poor sharks were isolated and became extinct in nothing flat. (They died looking for food I believe.) So it was interesting to see that the fish and plants still maintained a harmonic.

 

I had never thought about how populations in nature likely are on such a clearly sinusoidal pattern. While watching the actual locations it is not nearly so obvious. It just looks like "business as usual". Though I am able to see at times that one or the other population is growing larger.

 

I wonder what future patterns we will see in ourselves when all humans are RFID tagged.

[Pyrotex]

What was the inspiration / reason for the Ocean simulator?

I was reading the book "Chaos--The Birth of a New Science".

There was a chapter on the inter-dependence of populations and birth rates.

I thought about modeling then, but got stuck. Years later, I attacked it again just to see if I could. I could. :singer:

 

Yeah, on rare occassions, the sharks starve. The reason is that I can't figure out how to program them with a sense of smell. :)

[Pyrotex]

This one is esoteric, abstruse and arcane. Other than that, it should be very useful!

It is a Parameter Index Constructor.

 

You know how you build all these equations into a really complex spreadsheet and then you have a problem with one, say, it's called fotjpi2, and you need to know every cell where that named parameter is used? Does that experience ring a bell? You ever have that problem? Well, do you, Bubbie?

 

I guess not. Well... sometimes, I do. Here is a solution of sorts. It is a method whereby you can get Excel itself to build you an index of all your parameters with addresses of every location where an equation uses them.

 

Sound like fun?

 

I guess you have to be there.

[Pyrotex]

This is a simple one, and yet chillingly profound.

It is the Cosmic Font of Random Truth.

 

Impressed, aren't you? :) It uses simple equations and functions that manipulate text rather than numbers.

[Symbology]

I was reading the book "Chaos--The Birth of a New Science".

There was a chapter on the inter-dependence of populations and birth rates.

I thought about modeling then, but got stuck. Years later, I attacked it again just to see if I could. I could. :D

Nice. I appreciate the concrete lesson!

 

Yeah, on rare occassions, the sharks starve. The reason is that I can't figure out how to program them with a sense of smell. :eek:

Hint: Smell generally comes from something leaving (an apparently hidden) trail.

:) As in "I smell a rat" or "I smell trouble" or "something smells fishy here" :singer:

 

"Decay" is another handy word. Although it takes more memory of course.

[Pyrotex]

This one is oh, so simple. Basically, it just iteratively calculates a simple equation 1,000 times and plots the results. But it allows you to stare directly into the eyes of Chaos itself.

 

I'm not kidding. Really. I mean it. I'm dead serious about this.

 

Yeah, "Chaos", the real thing. The mathematical entity. This equation comes right out of the book, "Chaos--The Birth of a New Science". Spend enough time with this spreadsheet and you will know Chaos intimately. Assuming you're still sane.

 

Such a dirt simple equation. It should create such dirt simple plots.

Really.

There's no rational reason for it to suddenly start giving you two solutions instead of one. Then four solutions. Then eight. Then... Then...

 

Then... :singer:

 

Then... omigod... OMIGOD... IT'S... CHAOS!!!!!! :)

 

No, really... I'm dead serious...