Pyro's Excel Goodies

[Pyrotex]

Originally Posted by Symbology

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.

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

[freeztar]

Originally Posted by 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.

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?

[freeztar]

Originally Posted by 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? 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?

[Pyrotex]

Originally Posted by freeztar

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

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]

Originally Posted by Pyrotex

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???
Is that some type of conditional cell formatting?

Oh yeah, btw...

[Pyrotex]

Originally Posted by freeztar

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???
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!!
You raised the desired budget cap!!!!!!!!

[freeztar]

Originally Posted by Pyrotex

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