Pyro's Excel Goodies

[Pyrotex]

Speaking of Excel spreadsheets... :evil:

 

I once made a challenge to a co-worker back when I was building databases for Doctors Without a Clue. I told her that no matter what she needed to do with her experimental data, no matter how it needed to be sliced, diced and spliced, I could do it for her in a spreadsheet.

 

If memory serves me correct (and it does!) I never failed one of her challenges.

 

If anyone cares... [yawn] I would be happy to make the same offer here for Hypo Regulars.

 

Lay on McDuff and damned be he who first cries, Hold! Enough! :lol:

[Symbology]

Speaking of Excel spreadsheets... :doh:

 

I once made a challenge to a co-worker back when I was building databases for Doctors Without a Clue. I told her that no matter what she needed to do with her experimental data, no matter how it needed to be sliced, diced and spliced, I could do it for her in a spreadsheet.

 

If memory serves me correct (and it does!) I never failed one of her challenges.

 

If anyone cares... [yawn] I would be happy to make the same offer here for Hypo Regulars.

 

Lay on McDuff and damned be he who first cries, Hold! Enough! :doh:

 

Yes but just because you can, doesn't mean you should

 

I have seen, and can think of many other cases that can be done in Excel that should be done in a real database.

 

One of the key problems with using Excel for data entry is also one of it's key features: It will allow any trash data in any given cell. This means when analysis or importing into other tools is to be done, in many cases it is stopped dead in its tracks by the trash text in the numeric column of data. Which will then have to be found by hand and corrected by hand.

Those leeetle green triangles turn me so red!

/forums/images/smilies/mad_2.gif

 

However I can appreciate your perspective in that if the data entry person is knowledgeable of their domain, conscientious, and diligent, then Excel can be used very quickly and efficiently to solve most any analysis problem.

 

It reminds me of the bet that I won in college that I could implement Karnaugh Map logic in Basic on the mainframe. I ended up turning in the fewest lines of code for the class that still accomplished the task.

 

However I am sure that Buffy would note that is another case of "Just because you can, doesn't mean you should"

[Pyrotex]

Okie dokie...

it's been awhile, but here is a very useful spreadsheet. It takes your raw data and constructs a Histogram.

 

Now you say, "whassa big deal, dude? Anybody can do bar charts!"

 

:doh: no no no no... A histogram is NOT just another bar chart. What it does is count how many data values (in your raw data) that fall in each of several ranges. For example, how many of your data values fall between:

0-9, 10-19, 20-29, etc.

 

Now, boyz unt gurlz, what if you want your histogram FULLY DYNAMIC ???

{dub in Twilight Zone theme}

 

Like, moving the lower limit of your histogram, changing the range-widths of the bars, adding or subtracting a bar, or two? At the click of a button?

 

Gotcha covered! :hihi: :doh: :)

 

Comes complete with sample data.

[freeztar]

That's pretty cool Pyro! :doh:

Have you used this data to determine the best time to leave for work? I guess you would have to graph 'leave time' against 'travel time'.

 

That's quite a commute! :hihi:

[Pyrotex]

That's pretty cool Pyro! :)

Have you used this data to determine the best time to leave for work? I guess you would have to graph 'leave time' against 'travel time'.

That's quite a commute! :doh:

Yes it was. From Clear Lake City to the downtown Houston Medical Center, a total of about 22 miles. Most of that was on Interstate-45, which can slow waaaaay down in the morning.

 

The histogram spreadsheet I present here is actually a cleaned-up subsection of a huge spreadsheet with the SAME data, but nearly a dozen histograms and scatterplots with trend lines. So, yes I did calculate the best time to leave home. Earlier. :hihi: And the days of the week with shortest commute (Thursday). And several other correlations. But it was too big, messy and ugly to post here.

[LaurieAG]

However I can appreciate your perspective in that if the data entry person is knowledgeable of their domain, conscientious, and diligent, then Excel can be used very quickly and efficiently to solve most any analysis problem.

...

However I am sure that Buffy would note that is another case of "Just because you can, doesn't mean you should" :QuestionM

 

Hi Symbology,

 

I'm glad you said 'most any problem' because problems with extremely large amounts of data would cause definite problems. in Excel.

[Pyrotex]

...problems with extremely large amounts of data would cause definite problems. in Excel.

Yes, of course. As a rule of thumb, I have serious second thoughts about using Excel for a data set that exceeds 1,000 rows, or that requires more than 100 columns for analysis, categorization, merging, etc.

 

Excel WILL handle far more data than that, BUT... the probability of making an error and the difficulty of finding your errors becomes just too too huge. It also makes Excel uncomfortably slow.

 

For a data set between 1,000 and 100,000 rows (records), I prefer to have the data in mySQL or some other speedy database, and write a PERL script to do the analysis.

 

For over 100,000 records, you can't beat SAS.

[Pyrotex]

Here are valuable resources for you spreadsheet geek wannabees (SGW) out there.

 

How I got so good at Excel was that as I picked up tricks and techniques, I kept a "cheat sheet" for myself. This eventually grew and evolved into an ~80 page manual for Achieving Excel Mastery. Here and now, I give away free copies of this manual, divided into two parts for your convenience.

 

If you want to know how to use Equations and built-in Formulas, then check out part 1: Basics of Excel Equations and Formulas.

[Pyrotex]

And here is the second part:

 

Basics of Excel Macros (VBA).

[Grains]

First of all I want to say that I have reviewed your excel work and it is amazing. I have been working with vba/excel for the past 2 years and I am still scratching the surface.

 

I was looking at your document for investment/retirement planning and had some input/challenge.

 

Is it possible to have one page where the user inputs data such as current age, retirement age, investments in qualified accounts (IRA, SEP, etc), investments in non-qualified accounts (regular account, etc), inflation, prediction of return on investments, prediction of return on investments during retirement, and annual income or annual income percentage needed in retirement.

 

The reason I ask is when you have qualified accounts your return will differ as if you had those assets in non-qualified accounts. And inflation will obviously have an effect on income needed today (user input) and in 20 years what the inflation income adjusted income they will need in 20 years. for example if today i need 100k in todays income in 20 years that might be like needing 200k.

 

I have tried for a while to encompass the effects of non-qualified vs qualified accounts as well as inflation and other measures to create a retirement plan in excel.

 

Have any ideas???

[Grains]

the other thing is a chart showing when i start taking income/withdrawals from my retirement account how will those adjust year to year as inflation increases the amount i need

[Pyrotex]

...Is it possible to have one page where the user inputs data such as current age, retirement age, investments in qualified accounts (IRA, SEP, etc), investments in non-qualified accounts (regular account, etc), inflation, prediction of return on investments, prediction of return on investments during retirement, and annual income or annual income percentage needed in retirement...

When asked any question about Excel which begins, "Is it possible to..." -- my standard answer is "Absolutely Yes".

 

My invest/retire spreadsheet basically calculates just one "account", which grows at one rate, and which is calculated in one column.

 

On a separate page (call it "Accounts"), why don't you devote one (or more) columns for calculating each of the types of accounts you mentioned. You could have your IRA calculated month by month in column B, your SEP in column D, your savings in column F, your Swiss bank account in column H, ... etc.

 

Then on my page where all the calculations take place, replace the equation in the investment column with the new equation:

=Accounts!B7 + Accounts!D7 + Accounts!F7 + Accounts!H7 + ... etc.

 

So, where *I* calculate just ONE account, make it where it adds up ALL your accounts.

 

You can easily create a new page, (call it "Inputs") where you define each of the variables the user should enter, such as current value of IRA, predicted return of IRA, current value of SEP, predicted return of SEP, ... etc.

 

Name these input cells. Give them names like: IRAstart, IRArate, SEPstart, SEPrate, ... etc.

 

Then on the Accounts page (where you calculate all your accounts), wherever you need one of your inputs in an equation, just use the name instead of a cell address.

[alexander]

You are the person i can ask this, i know you'd know a hell of a lot better then me. Can you write a macro for excel that will collapse certain fields to be invisible, and then next time toggle them back, or at least one macro to collapse the fields, and another to uncollapse them?

 

here's the problem, our av team has a form that they use universally to price things, what you do is put in say the name of the product, their cost, mark up, and it will calculate a few other values from it. They also use some checking systems etc for the final form, to them they want to see everything, but when they output the form to pdf, they want to hide a few fields that customers don't need to see. now they have to do them one by one, but i was hoping you'd have some time to work some magic, and perhaps propose some sort of a really cool solution (such as some of the crazy things you've done here :) )

 

I have faith in your superior excel knowledge :)

 

And thanks in advance for the help, or even just looking into this and telling me that it cant be done.

[Pyrotex]

You are the person i can ask this, i know you'd know a hell of a lot better then me. ...And thanks in advance for the help, or even just looking into this and telling me that it cant be done.

 

Alex,

my standard answer is "Absolutely Yes", it can be done.

Please read the PM I sent with questions that need answers before I can dazzle you with my Excel Wizardry. :naughty:

Pyro

[Grains]

pyro...ive been working with some vba macros and wanted to see if you could help me with a few scripts...

 

1) If I have a word document with a whole bunch of names and addresses and want to pull it over to excel is there a way to write a macro that will pull all the data over on to the sheet. I know there is and I know how to do that part here is where it gets a little tricker. Each word document might have different amounts of pages and addresses . I have written a macro for one word file but I try it in a different word file it dosn't work because different amounts of data. Any suggestions?

 

2) When the data comes over from word it is very discombobulated. For example like this

 

A1

 

Sam Nobody

113 Lake View Dr

Atlanta, GA 30328

 

So I wrote a macro that does this to that data

 

A1 A2 A3

Sam Nobody 113 lake view.. Atlanta, GA 30328

 

Reason I am doing this is so we can do mailing labels. Problem is I have to run that macro for each individual person...Not hard just time consuming and I know there should be a way to automate it. Its just the problem is that when the data comes over from word it also brings about random spaces of rows between each entry...so one entry to the next might be 2 rows where the next one could be 5.....does that make since...

 

Any help would be appreciated..

[Pyrotex]

...1) If I have a word document with a whole bunch of names and addresses and want to pull it over to excel

...2) When the data comes over from word it is very discombobulated. ...

Hello Grains,

There are 4 ways to get the data to Excel:

1. Easiest--just copy the data from Word and paste it into Excel.

2. Easy--go to Excel and "import" the data from the Word doct by using the menu command (from Excel).

3. Hard--write an Excel macro to transfer the data from the Word doct.

4. Hardest---write a macro in Word to transfer the data.

 

All of these techniques depend on the data being well formatted in Word.

You need to make sure that every name/address has exactly the same number of lines; every name/address is separated by the same number of blank lines; every line of the name/addresses end in a full paragraph character (The Enter Key) and not a "new-line" character. So your best bet is to neaten up the Word file FIRST.

 

Then, technique 1 or 2 works like a snap without having to write any macros.

[Pyrotex]

...Can you write a macro for excel that will collapse certain fields to be invisible, and then next time toggle them back, or at least one macro to collapse the fields, and another to uncollapse them?....

YES!! And here is how it is done.

 

The solution involves doing three things:

1. Define range names to store the widths of your hide-able columns.

2. Create HIDE and REVEAL macros.

3. Create HIDE and REVEAL buttons.

 

1. I am assuming you want to hide one or more entire columns. Let us assume you want to hide columns B, C and D. Somewhere on that same page of your spreadsheet, out of the way of all your data, build a small table with the following Range-Names:

ColBwid

ColCwid

ColDwid

Our little table will (in this case) be three rows long, and exactly two columns wide. The cells to the right of our three names should be blank and unused.

 

Select all cells containing our Range-Names --AND-- the blank cells to their right. In my case, I am selecting 6 cells.

 

Pull down menu:Insert:Name:Create, and make sure the box "Left" is selected. Click OKAY. You have now created 3 Range-Names in your spreadsheet. This is where the HIDE macro will store the current sizes of your columns.

 

2. Pull down menu:Tools:Macro:Visual Basic Editor. The Project Window should be on the left. In there you will see the name of your spreadsheet. Under that is a folder for "Objects" and a folder for "Modules". Open the latter. If there is no Module, then pull down menu:Insert:Module. The blank module will appear on the right. In the module type (or copy and paste) your two Macros:

 

Sub Hider()
   Range("B:B").Select
   Range("ColBwid").Value = Selection.ColumnWidth
   Range("C:C").Select
   Range("ColCwid").Value = Selection.ColumnWidth
   Range("D:D").Select
   Range("ColDwid").Value = Selection.ColumnWidth
   Columns("B:D").Select
   Selection.ColumnWidth = 0  'Hide the columns!
End Sub

Sub Revealer()
   'Restore the columns!
   Range("B:B").Select
   Selection.ColumnWidth = Range("ColBwid").Value
   Range("C:C").Select
   Selection.ColumnWidth = Range("ColCwid").Value
   Range("D:D").Select
   Selection.ColumnWidth = Range("ColDwid").Value
End Sub

 

3. Pull down menu:Tools:Customize, select the tab "Toolbars". Click the box "Forms". Click CLOSE. The Forms toolbar should now be visible. We want only one thing off that toolbar, the rectangle icon, which creates buttons. Pick a place to put two buttons. I suggest immediately above or below your Range-Names. Allocate a 4-cell block (2 by 2) for each button.

 

Click the button icon on the Forms toolbar. With your "cross" cursor draw your first button in a 4-cell block. Repeat for your second button.

 

If you Right_Click either button, it will allow you to change it, resize it, move it, select and retype its name, assign its macro. Change the names to HIDE and REVEAL. Click some cell away from the buttons so they're not selected.

 

Again, Right_Click each button in turn and select "Assign Macro". Pick the corresponding macro for each button. Click some cell away from the buttons so they're not selected.

 

That's All, Folks! Make sure you click the HIDE button first so the Range-Names will be initialized. The REVEAL button will re-expand the columns to the same width they were before. I thought you would like that. :)