H
Harlan Grove
(e-mail address removed) wrote...
....
What about those using Excel on Macs?
MSDE is free.
Now how about business PC users who work for companies that
*EXPLICITLY* prohibit them from installing nonapproved software on
their PCs? Unless their IT department installs MSDE, they can't use it
without putting their jobs at risk.
And would everyone have their own copy of MSDE on each of their
machines with all their own tables only on their own PCs? And how would
this address your stated concern about data islands?
I already pointed out the foolishness of trying to use a cartesian for
this. See
http://groups.google.com/group/microsoft.public.excel/msg/2c1e73ffd974b4d2?dmode=source&hl=en
(or http://makeashorterlink.com/?I3B12499B ). For permutations of 8
tokens, this requires 16,777,216 cartesian product nodes for 40,320
permutations. BTW, how would one do this *WITHOUT* the OLAP add-in? For
that matter, how would one do it *WITH* the OLAP add-in, i.e., where's
the trivially simple code/instructions to condense the 16,777,216
cartesian product nodes (8^8) into 40,320 permutations of 8 distinct
tokens (8!)?
As for higher order permutations, it may be a pain to do them in Excel
due to the 64K row limit, but it's still possible without unduly
wasting system resources. There are 3,628,800 permutations of 12
distinct tokens (10!). That'd require 56 worksheets if only 10 columns
were used in each worksheet or 3 worksheets if permutations were listed
in columns A-J, L-U, W-AF, etc. in each worksheet. A cartesian product
approach would require 10^10 nodes, which would tax the virtual memory
capacity of most PCs currently in use.
So you could try to do it using cartesian products, but even you would
discover that it's nowhere near simple that way, and you'd run out of
system resources long before a factorial order algorithm in Excel
would.
You disagree?! No! Really?!
Which is why most of Excel's functions that take range or array
arguments ignore blanks. Also, it's not that hard to use dynamically
resizable ranges in Excel.
If you know what you're doing in Excel (as you obviously don't), you
can deal pretty easily with changing numbers of data entries. And data
doesn't have to be in record form.
Excel and databases provide the *SAME* basic math functionality.
Arithmetic and simple aggregation functions, e.g., SUM, COUNT, AVERAGE,
MIN, MAX, etc. Access even provides many built-in functions similar to
Excel's built-in functions.
However, it's not the functions and operators that hinder dbms's for
math, it's the lack of simple indexing and recursion constructs. Take
another look at the queries *I* provided to generate amortization
tables. You consider them simple compared to the Excel formulas I also
provided in contrast? If you could come up with a simpler database
aproach, PROVE IT and post your instructions.
17 years ago I produced a particular report that contained a grand
total of 7 numbers. 4 of those numbers came from a mainframe report to
which the then named MIS department was unwilling to provide online
access (via mainframe terminal emulation and access rights to view
archived print jobs stored on tape). One of the numbers came from the
previous report (and the 123 report template workbook had a \0 macro to
pull it automatically). The last two numbers were formula-generated,
and the formulas never changed (they were perverse proration formulas).
Could this have been completely automated? Yes. However, the MIS
department at the time was unwilling to do so. Could the department in
which I worked have used a database to have automated this? Possibly,
but it would have taken more time and effort to have loaded a dbms and
entered the data into it then generated the report than it did to open
the 123 workbook and let it's \0 macro pull in one number from the
prior report, script user entry of the 4 new entries from the mainframe
report using {?}~ macro statements (typing would be identical in
spreadsheet and dbms), then automatically saving the new report and
printing it.
There's a remote chance a dbms could have created this particular
report as quickly as a spreadsheet could (on the spreadsheet side the
greatest amount of time was spent entering the 4 numbers), but there's
no way a dbms could have produced it more quickly.
Normalization is explicitly unwelcome in Excel. Most of the data
structures uses in spreadsheets look like small scale pivot tables or
crosstabs, which are decidedly not normalized but are much more
efficient data structures in spreadsheets given the way spreadsheets
dereference spreadsheet data.
And the lack of mechanisms to enforce referential integrity is what
makes spreadsheets far more flexible than dbms's.
If you view everything as a potential database, normalization and
referential integrity are essential. Referential integrity is also
essential in spreadsheets, but it needs to be enforced by the
user/developed because spreadsheets can't provide mechanisms to enforce
it without sacrificing a considerable part of what makes them more
suitable to particular calculation-intensive tasks than dbms's.
Much of it is. Databases do make good data storage subsystems.
Agreed. Repetitive tasks in which nothing changes other than data
that's available from central databases are the ideal example of what
databases do well (much better than spreadsheets). You're right about
that. Where you're dead wrong is believing that makes up most of
business PC use.
Well, not for you certainly since you couldn't tell your fundamental
orifice from a circular reference.
More repeated misinformation. They're still #3.
Ignorance is such bliss! You have no clue, and you're proud of it!
You really don't understand that most people aren't hired specifically
to write code or build applications.
It might take me a few weeks to figure out how to do your job. You
couldn't figure out mine if you had years to do so.
We all are. However, you're interchangeable with a lot of other
database grunts.
....
What about those using Excel on Macs?
MSDE is free.
Now how about business PC users who work for companies that
*EXPLICITLY* prohibit them from installing nonapproved software on
their PCs? Unless their IT department installs MSDE, they can't use it
without putting their jobs at risk.
And would everyone have their own copy of MSDE on each of their
machines with all their own tables only on their own PCs? And how would
this address your stated concern about data islands?
I already pointed out the foolishness of trying to use a cartesian for
this. See
http://groups.google.com/group/microsoft.public.excel/msg/2c1e73ffd974b4d2?dmode=source&hl=en
(or http://makeashorterlink.com/?I3B12499B ). For permutations of 8
tokens, this requires 16,777,216 cartesian product nodes for 40,320
permutations. BTW, how would one do this *WITHOUT* the OLAP add-in? For
that matter, how would one do it *WITH* the OLAP add-in, i.e., where's
the trivially simple code/instructions to condense the 16,777,216
cartesian product nodes (8^8) into 40,320 permutations of 8 distinct
tokens (8!)?
As for higher order permutations, it may be a pain to do them in Excel
due to the 64K row limit, but it's still possible without unduly
wasting system resources. There are 3,628,800 permutations of 12
distinct tokens (10!). That'd require 56 worksheets if only 10 columns
were used in each worksheet or 3 worksheets if permutations were listed
in columns A-J, L-U, W-AF, etc. in each worksheet. A cartesian product
approach would require 10^10 nodes, which would tax the virtual memory
capacity of most PCs currently in use.
So you could try to do it using cartesian products, but even you would
discover that it's nowhere near simple that way, and you'd run out of
system resources long before a factorial order algorithm in Excel
would.
You disagree?! No! Really?!
Which is why most of Excel's functions that take range or array
arguments ignore blanks. Also, it's not that hard to use dynamically
resizable ranges in Excel.
If you know what you're doing in Excel (as you obviously don't), you
can deal pretty easily with changing numbers of data entries. And data
doesn't have to be in record form.
Excel and databases provide the *SAME* basic math functionality.
Arithmetic and simple aggregation functions, e.g., SUM, COUNT, AVERAGE,
MIN, MAX, etc. Access even provides many built-in functions similar to
Excel's built-in functions.
However, it's not the functions and operators that hinder dbms's for
math, it's the lack of simple indexing and recursion constructs. Take
another look at the queries *I* provided to generate amortization
tables. You consider them simple compared to the Excel formulas I also
provided in contrast? If you could come up with a simpler database
aproach, PROVE IT and post your instructions.
17 years ago I produced a particular report that contained a grand
total of 7 numbers. 4 of those numbers came from a mainframe report to
which the then named MIS department was unwilling to provide online
access (via mainframe terminal emulation and access rights to view
archived print jobs stored on tape). One of the numbers came from the
previous report (and the 123 report template workbook had a \0 macro to
pull it automatically). The last two numbers were formula-generated,
and the formulas never changed (they were perverse proration formulas).
Could this have been completely automated? Yes. However, the MIS
department at the time was unwilling to do so. Could the department in
which I worked have used a database to have automated this? Possibly,
but it would have taken more time and effort to have loaded a dbms and
entered the data into it then generated the report than it did to open
the 123 workbook and let it's \0 macro pull in one number from the
prior report, script user entry of the 4 new entries from the mainframe
report using {?}~ macro statements (typing would be identical in
spreadsheet and dbms), then automatically saving the new report and
printing it.
There's a remote chance a dbms could have created this particular
report as quickly as a spreadsheet could (on the spreadsheet side the
greatest amount of time was spent entering the 4 numbers), but there's
no way a dbms could have produced it more quickly.
Normalization is explicitly unwelcome in Excel. Most of the data
structures uses in spreadsheets look like small scale pivot tables or
crosstabs, which are decidedly not normalized but are much more
efficient data structures in spreadsheets given the way spreadsheets
dereference spreadsheet data.
....
And the lack of mechanisms to enforce referential integrity is what
makes spreadsheets far more flexible than dbms's.
If you view everything as a potential database, normalization and
referential integrity are essential. Referential integrity is also
essential in spreadsheets, but it needs to be enforced by the
user/developed because spreadsheets can't provide mechanisms to enforce
it without sacrificing a considerable part of what makes them more
suitable to particular calculation-intensive tasks than dbms's.
Much of it is. Databases do make good data storage subsystems.
Agreed. Repetitive tasks in which nothing changes other than data
that's available from central databases are the ideal example of what
databases do well (much better than spreadsheets). You're right about
that. Where you're dead wrong is believing that makes up most of
business PC use.
Well, not for you certainly since you couldn't tell your fundamental
orifice from a circular reference.
More repeated misinformation. They're still #3.
Ignorance is such bliss! You have no clue, and you're proud of it!
You really don't understand that most people aren't hired specifically
to write code or build applications.
It might take me a few weeks to figure out how to do your job. You
couldn't figure out mine if you had years to do so.
We all are. However, you're interchangeable with a lot of other
database grunts.