Jump to content
Science Forums

Pyro's Excel Goodies


Recommended Posts

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!!!!!! :yeahthat:

 

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

 

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

Link to comment
Share on other sites

...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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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? :yeahthat: 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:

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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!!!!!!!!

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...