Pyro's Excel Goodies

[alexander]

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

[C1ay]

Originally Posted by alexander

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

[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]

Originally Posted by C1ay

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

[Pyrotex]

Speaking of Excel spreadsheets...

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!

[Symbology]

Originally Posted by Pyrotex

Speaking of Excel spreadsheets...

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!

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!

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

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!

Comes complete with sample data.

[freeztar]

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!

[Pyrotex]

Originally Posted by freeztar

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!

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

Originally Posted by Symbology

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"

Hi Symbology,

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