Pyro's Excel Goodies

[Jay-qu]

Excellent Pyro, I remember being thrilled with one of your 'chaos generators' you posted a while back :)

 

Question can the drunkards walk to likened to brownian motion?

[Symbology]

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... :phones:

 

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

 

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

 

Interesting how there are moments of calm in the storm like going from 3.9601 to 3.9602

[Pyrotex]

...Question can the drunkards walk to likened to brownian motion?

Thank you.

Yes.

An odd result of statistics as applied to the drunkard's walk: the average distance the drunkard actually travels from the starting point after N random steps is around the square root of N.

Hmmm.

Makes you think, don't it?

[Pyrotex]

Interesting how there are moments of calm in the storm like going from 3.9601 to 3.9602

Absolutely. That is what makes it chaotic.

 

It's also interesting that at the beginning, you get 2-symmetry, 4-symmetry, 8-symmetry, etc, before everything gets crazy. But in those islands of calm, you see 3-symmetry, 10-symmetry, 7-symmetry...

[Pyrotex]

Here is an oldie goldie. It is called Cost Leveling, and what it does is,... uh... level costs. Its all explained on the first page.

 

The history of how I created this may be interesting. The year was 1986, and I had been hired by McDonnell Douglas during the proposal competition phase of the Space Station Freedom program. Our competitors were Rockwell and Lockheed (I think). When I had completed my initial assignment, I begged and begged to be assigned to SSF. They did, but there was only one opening left!

 

Writing the Costing proposal.

 

No engineer wants to do the Costing proposal. Boring. But it WAS the only slot left, and so... I accepted. How the FREAKIN HELL do you "cost" a space station? I did not have a clue. So, I whupped out my Excel and started playing around. Somebody gave me an old dog-eared, faded copy of an article from some engineering magazine dated back 15 years or so.

 

And I created a version of this spreadsheet, only explicitly for costing SSF. Each sub-project was a component of the space station. Each sub-project was calendared to "complete" on the month of its launch into orbit. By shifting launch dates, I was able to demonstrate how launch schedule was driven by annual budget caps.

 

My Costing proposal beat the other two hands down. Corporate Systems Engineering adopted my spreadsheet approach as a standard costing/scheduling tool. My results were accepted by the U.S. Congress, and published in Aviation Week and Space Technology.

 

I got my 15 minutes of fame. Twice. :phones:

[Pyrotex]

Are you approaching the September Days of your life? I sure as hell am. Well, here is little program that may help you out.

It is a simple Investment Retirement Planner.

 

You just plug in how much a month you are squirreling away, what your rate of return is (good luck on that!), the rate of inflation, how many months until you retire, and how much a month you will need to retire on.

 

The program will give you a lovely chart showing how much will be in your retirement account up to and beyond retirement. It's amazing how it disappears so fast. :(

 

Anyway, there is nothing "simple" about this spreadsheet. There are juicy tricks galore! I calculate month by month, but I plot year by year. How does he do that? :eek_big: Take a peek and watch as I use Excel to calculate cell addresses and fetch values indirectly.

 

Oh, dat Pyro, he be one sneaky wizard! BWAHAHAHAHA! :lol:

[freeztar]

Here is an oldie goldie. It is called Cost Leveling, and what it does is,... uh... level costs. Its all explained on the first page.

 

The history of how I created this may be interesting. The year was 1986, and I had been hired by McDonnell Douglas during the proposal competition phase of the Space Station Freedom program. Our competitors were Rockwell and Lockheed (I think). When I had completed my initial assignment, I begged and begged to be assigned to SSF. They did, but there was only one opening left!

 

Writing the Costing proposal.

 

No engineer wants to do the Costing proposal. Boring. But it WAS the only slot left, and so... I accepted. How the FREAKIN HELL do you "cost" a space station? :eek_big: I did not have a clue. So, I whupped out my Excel and started playing around. Somebody gave me an old dog-eared, faded copy of an article from some engineering magazine dated back 15 years or so.

 

And I created a version of this spreadsheet, only explicitly for costing SSF. Each sub-project was a component of the space station. Each sub-project was calendared to "complete" on the month of its launch into orbit. By shifting launch dates, I was able to demonstrate how launch schedule was driven by annual budget caps.

 

My Costing proposal beat the other two hands down. Corporate Systems Engineering adopted my spreadsheet approach as a standard costing/scheduling tool. My results were accepted by the U.S. Congress, and published in Aviation Week and Space Technology.

 

I got my 15 minutes of fame. Twice. :(

 

Cool story! :)

 

I tried to do your challenge, but copying and pasting the green boxes messes up the graph. Do I have to do it manualy and change the equations for each box? :lol:

[freeztar]

Are you approaching the September Days of your life? I sure as hell am. Well, here is little program that may help you out.

It is a simple Investment Retirement Planner.

 

You just plug in how much a month you are squirreling away, what your rate of return is (good luck on that!), the rate of inflation, how many months until you retire, and how much a month you will need to retire on.

 

The program will give you a lovely chart showing how much will be in your retirement account up to and beyond retirement. It's amazing how it disappears so fast. :)

 

Anyway, there is nothing "simple" about this spreadsheet. There are juicy tricks galore! I calculate month by month, but I plot year by year. How does he do that? :lol: Take a peek and watch as I use Excel to calculate cell addresses and fetch values indirectly.

 

Oh, dat Pyro, he be one sneaky wizard! BWAHAHAHAHA! :(

 

Apparently, I "ran out". :(

 

How come my R/A is never >0?

:eek_big:

[Pyrotex]

...I tried to do your challenge, but copying and pasting the green boxes messes up the graph. Do I have to do it manualy and change the equations for each box? :eek_big:

Changing the schedule for completing a sub-proj is a 2-step operation:

 

1) Copy a blank green cell from the table and paste over the "12" that flags the (old) completion of a sub-proj.

 

2) Manually type in "12" in a new month for that same sub-proj, which defines a new completion date. (all within the green table, of course)

 

No changing equations! No messing with input values.

 

PS: I downloaded the Hypo version of Cost Leveler and tried it. It works.

[freeztar]

Changing the schedule for completing a sub-proj is a 2-step operation:

 

1) Copy a blank green cell from the table and paste over the "12" that flags the (old) completion of a sub-proj.

 

2) Manually type in "12" in a new month for that same sub-proj, which defines a new completion date. (all within the green table, of course)

 

No changing equations! No messing with input values.

 

PS: I downloaded the Hypo version of Cost Leveler and tried it. It works.

 

 

Ok, I see what you mean.

I get how the "=if(y17>0,y17-1,0)" equation works, but I don't get how when you type 12 in any cell, it automatically creates 1-11??? :eek_big:

Is that some type of conditional cell formatting?

 

Oh yeah, btw... :lol:

[Pyrotex]

Ok, I see what you mean.

I get how the "=if(y17>0,y17-1,0)" equation works, but I don't get how when you type 12 in any cell, it automatically creates 1-11??? :eek_big:

Is that some type of conditional cell formatting?

 

Oh yeah, btw... :(

 

The 1-11 is generated by the field of equations to the immediate left of the "12". Not very complicated. The only formatting involved is that zeros are not displayed.

 

Oh yeah, btw... you cheated!! :lol: :( :)

You raised the desired budget cap!!!!!!!!

[freeztar]

The 1-11 is generated by the field of equations to the immediate left of the "12". Not very complicated. The only formatting involved is that zeros are not displayed.

 

Ok, I see now. Neat trick.

Oh yeah, btw... you cheated!! :eek_big: :lol: :(

You raised the desired budget cap!!!!!!!!

 

:(

 

Does it count as cheating if you have no flipping idea what you did? :)

 

But seriously, I didn't even notice that. Why/how did it change? I only messed with the green cells under "Calender".

[alexander]

Pyro! need thy help, oh guru of excel wisdom

 

emmk, have a spreadsheet that has about 4500 lines of ip addresses (split over a couple of work sheets) (that repeat pretty commonly), idea was to make another sheet and add a list of ips/host names to it. the second field of the spread sheet with ip's needs to contain a function similar to =LOOKUP(A3;domain_list.A1:A250;domain_list.B1:B250)

 

Problem:

replicating this function field throughout 4500 lines

when you drag this function field, or a set of these fields, it starts incrementing, not only the first part of that lookup command, but also it starts shifting ranges, and that pisses me off sooo badly, gaaaah.

 

so, can you use some of your wisdom to help me solve this otherwise simple problem of having to basically go over the entire range of 4500 addresses and edit every line?

[alexander]

wow that was simple.... absolute cell references are your friend.

[C1ay]

emmk, have a spreadsheet that has about 4500 lines of ip addresses (split over a couple of work sheets) (that repeat pretty commonly)....

 

Something else you might find useful is using SQL with Excel via MSQuery. You can use Data>>Get External Data>>New Database Query to open it and then select "Excel Files" and then navigate to the Excel Workbook you have open. Then you can use statements like: SELECT DISTINCT .... to get the unique rows from your spreadsheet :eek2:

[Symbology]

Pyro,

 

Thanks for posting all the great code exercises. They serve as an excellent example of how to move towards computer mastery with both "Visualization" and "Practice Practice Practice" from the 5 Keys to Mastery.

 

With appreciation,

Symbology

[alexander]

Something else you might find useful is using SQL with Excel via MSQuery. You can use Data>>Get External Data>>New Database Query to open it and then select "Excel Files" and then navigate to the Excel Workbook you have open. Then you can use statements like: SELECT DISTINCT .... to get the unique rows from your spreadsheet

I know, i just don't care about this spread sheet that much c1ay, sad thing, all this work of getting the ips, importing them, creating this lookup thing, etc, all for maybe 5 minutes of the actual showing this crap... and that's it, it will never be revisited again...