Thread: The best elegant solution to override 65k rows limit in a sheet

and just for the record, Access ships with MSDE.. keeping your data in
a real database-- like MSDE means that you can grow up and use MDX when
you're ready. MDX is the 'spreadsheet language of the database world'

SQL Server Books Online (again-- Access ships with freeware SQL Server
engine)
Using WITH to Create Calculated Members mentions percentile

And here you go; these functions (except the ones with a *) are
supported in Analysis Services
Excel Functions
Microsoft=AE SQL Server=99 2000 Analysis Services supports many
functions in the Microsoft Excel worksheet library, which is
automatically registered if installed on the computer with Analysis
Services. Functions not supported in this release are marked by an
asterisk in this table.

Acos Acosh And *Application
Asc Asin Asinh Atan2
Atanh AveDev Average BetaDist
BetaInv BinomDist Ceiling ChiDist
ChiInv ChiTest Choose Clean
Combin Confidence Correl Cosh
Count CountA *CountBlank *CountIf
Covar *Creator CritBinom *DAverage
Days360 Db Dbcs *DCount
*DCountA Ddb Degrees DevSq
*DGet *DMax *DMin Dollar
*DProduct *DStDev *DStDevP *DSum
*DVar *DVarP Even ExponDist
Fact FDist Find FindB
FInv Fisher FisherInv Fixed
Floor Forecast *Frequency FTest
Fv GammaDist GammaInv GammaLn
GeoMean *Growth HarMean *HLookup
HypGeomDist *Index Intercept Ipmt
Irr IsErr IsError IsLogical
IsNA IsNonText IsNumber Ispmt
IsText Kurt Large *LinEst
Ln Log Log10 *LogEst
LogInv LogNormDist *Lookup Match
Max *MDeterm Median Min
*MInverse MIrr *MMult Mode
NegBinomDist NormDist NormInv NormSDist
NormSInv NPer Npv Odd
Or *Parent Pearson Percentile
PercentRank Permut Pi Pmt
Poisson Power Ppmt Prob
Product Proper Pv Quartile
Radians *Rank Rate Replace
ReplaceB Rept Roman Round
RoundDown RoundUp RSq Search
SearchB Sinh Skew Sln
Slope Small Standardize StDev
StDevP StEyx Substitute *Subtotal
Sum *SumIf SumProduct SumSq
SumX2MY2 SumX2PY2 SumXMY2 Syd
Tanh TDist Text TInv
Transpose *Trend Trim TrimMean
TTest USDollar Var VarP
Vdb *VLookup Weekday Weibull
ZTest

In other words-- you guys are roadkill since I can do all of this
'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
10mb spreadsheets

I can do all of this on the db server side of the equation-- so I can
do it faster than you can do it on the desktop.. and I can do it
against BILLIONS of records with a sub-second response time. (if you
know what you're doing-- like I do)

-aaron

aaron.kempf@gmail.com wrote...
>and just for the record, Access ships with MSDE.. keeping your data in
>a real database-- like MSDE means that you can grow up and use MDX when
>you're ready. MDX is the 'spreadsheet language of the database world'

Good for those with Office Pro. Not useful for those with other
versions of Office that don't come with Access. And MDX is yet another
product, and one that few if any business users outside IT departments
would have.


>SQL Server Books Online (again-- Access ships with freeware SQL Server
>engine)
>Using WITH to Create Calculated Members mentions percentile

You don't understand. The percentile of a Beta distribution
corresponding to probability p given parameters alpha and beta is given
by

BETAINV(p,alpha,beta)

>And here you go; these functions (except the ones with a *) are
>supported in Analysis Services
>Excel Functions
....

Yet more software the typical users won't have!

Winnowing the chaff,

*Application
*CountBlank
*CountIf
*Creator
*DAverage
*DCount
*DCountA
*DGet
*DMax
*DMin
*DProduct
*DStDev
*DStDevP
*DSum
*DVar
*DVarP
*Frequency
*Growth
*HLookup
*Index
*LinEst
*LogEst
*Lookup
*MDeterm
*MInverse
*MMult
*Parent
*Rank
*SumIf
*Trend
*VLookup

No big surprise the so-called database functions, {|H|V}LOOKUP,
COUNTBLANK, COUNTIF and SUMIF aren't provided since there are already
better ways to achieve their results through queries. Odd that MATCH is
included since it's pointless without INDEX, which isn't included, but
there's probably some form of arbitrary indexing provided. No big deal
about RANK since it could be implemented via queries.

FREQUENCY wouldn't be very much fun to implement in queries, but it
would be doable.

Lack of multiple regression functions would make it rather difficult to
general linear modeling.

Still, not bad. Now if it were free and could be used with other dbms's
than SQLServer . . .

>In other words-- you guys are roadkill since I can do all of this
>'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
>you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
>10mb spreadsheets

And you can't pitch a deal to customers. So we still make the big
bucks, and you're permanently parked in IT-land. Think of it this way:
the people who hire your boss's boss know spreadsheets, not databases.

>I can do all of this on the db server side of the equation-- so I can
>do it faster than you can do it on the desktop.. and I can do it
>against BILLIONS of records with a sub-second response time. (if you
>know what you're doing-- like I do)

You're full of it. You'd get quick responses either because your dbms
is spitting back cached results pulled in previously run queries, or
you're benefitting from indexing used to pull the thousands of records
you're actually selecting from the billions of records you claim to be
processing.

And again you're failing to understand that few business users outside
IT departments have access to those billion records as opposed to a few
views (just read-only access) with a few thousand records each that
their IT departments provide (and they were forced, kicking &
screaming, to provide even that).

If a user lacks rights to create even temporary tables on server-side,
just how much can that user really do?

aaron.kempf@gmail.com wrote...
....
>and then you can centralize your logic in one place; and when you need
>to change your calculation; you can do it all in one place.. instead of
>wading through 1000 different XLS formulas.. I mean..

You don't get it. Either the formulas and workbook templates don't
change, only the data entered changes (and IMO manual data entry is
slightly easier in Excel than Access, though there's something to be
said for input masks in Access; copy & paste entry from non-XLS,
non-tabular files is much easier in Excel than Access), in which case
don't-reinvent-the-wheel is operative; or the formulas aren't the same,
in which case saved previous business logic isn't useful.

>aren't you tired of having numbers that dont match?

And you've never come across situations in which two derived dbms
tables that should be showing the same results differ in some records?

Reconcilliation nightmares don't entirely disappear when one uses
dbms's.

>the root of the problem with Excel is that you have different formulas
>in each cell-- in Access you have a different formula for each COLUMN
>(or field.. whatever nomenclature you choose)

As I mentioned before, there have been lots of software products that
provided multidimensional database-like functionality, starting with
Lotus Improv (actually starting with the spreadsheet VP-Planner, but
its multidimensional database was rather limited). All were going to
kill spreadsheets as we know them. Most are not extinct and the
spreadsheet lives on & on. Rats outlived dinosaurs, and cockroaches
will likely outlive rats. Feel free to consider spreadsheets the
cockroaches of the software world.

There's a perception that the flexibility provided by spreadsheets,
with a single formula per cell, is worth it. Having a rough idea what
it took to tweak individual 'cell' results in Improv (it required
creating another dimension for exceptions, then incorporating the
exception dimension into the formulas), I can understand why such
programs have never sold well for general use.

>I just know that there is a better way for all you guys to spend your
>workdays.
....

You have no idea how we spend our workdays. You believe we spend our
time in the same report creation cesspool you do. Ain't so.

Harlan I fully agree with you, and that was the driver that made me ask the initial question.

Everyone has to remember that the relational paradigm in DMBS is far older than spreadsheets, and it's adn will remain an IT-Related issue for most of the cases.

The relational model can't represent reality and we see it every day through heavy customization of business apps (i mention SAP, Oracle, Peoplesoft etc...). Customers mostly aren't happy with them, so we have to switch to another paradigm closer and closer to customers needs.

Now i ask you a question... What if excel would support billions of records???

And seriously:

The relational model can't represent reality and we see it every day
through heavy customization of business apps (i mention SAP, Oracle,
Peoplesoft etc...). Customers mostly aren't happy with them, so we have

to switch to another paradigm closer and closer to customers needs

YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?

ACCESS DATA PROJECTS

all of your enterprise level apps should be written in ADP so that you
can make changes when you need to.. buying a closed system like SAP and
Peoplesoft.. i mean..

use the tools that are already on your desktop instead of buying a
billion dollars worth of 'enterprise level apps' that are closed; more
difficult to implement/troubleshoot

you really think that the 'relational model' is to blame because of the
fact that all your data is housed in data silos?

The answer is
ACCESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

rather than pulling data around; link to it.

<aaron.kempf@gmail.com> wrote in message
news:1120752903.274079.281500@g49g2000cwa.googlegroups.com...

> all of your enterprise level apps should be written in ADP so that you
> can make changes when you need to.. buying a closed system like SAP and
> Peoplesoft.. i mean..

You have out-done yourself with asinine comments with this one.

aaron.kempf@gmail.com wrote...
>The relational model can't represent reality and we see it every day
>through heavy customization of business apps (i mention SAP, Oracle,
>Peoplesoft etc...). Customers mostly aren't happy with them, so we have
>
>to switch to another paradigm closer and closer to customers needs
>
>YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
>SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?

??!

The relational model, if implemented with the full relational algebra
and calculus as spelled out in several books by E.F. Codd and C.J. Date
can represent any arbitrarily complex relationship between atomic data.
Some of those relationships could require rather complex many-to-many
relations, but theoretically it can handle them (given enough storage).

However, in the real world we have SQL. SQL isn't really standard. SQL
isn't orthogonal (see most of the books written by C.J. Date that
mention SQL). SQL doesn't implement the full relational algebra or
calculus.

The relational model may be just what everyone needs, but SQL and
SQL-based dbms's aren't panaceas.

>ACCESS DATA PROJECTS
>
>all of your enterprise level apps should be written in ADP so that you
>can make changes when you need to.. buying a closed system like SAP and
>Peoplesoft.. i mean..
....

If you define 'app' as report generation, perhaps.

And yet again more ignorance. The value of PeopleSoft and SAP comes
from the pre-built relations and (e.g., withholding taxes in payroll
systems) from pre-filled tables bundled with the product.

Bozos like you who like reinventing the wheel (re-entering tax tables,
recreating standard business relations, etc.) rather than buying
packaged solutions may prefer plain dbms's. Fortunately, bozos like you
don't make purchasing decisions.

aaron.kempf@gmail.com wrote...
>you still haven't told me how it is that you somehow dont recreate the
>whell 10 times a month
>
>YOU BUILD THE SAME SPREADSHEET EVERY WEEK

No, you just want to believe that.

I use the same spreadsheet templates several times a day, but only the
data changes, and the data comes from outside my company, so the only
electronic feed is from e-mailed PDF-to-clipboard-to-Excel. (Actually,
e-mailed PDF to pdf2txt to awk to CSV to Excel, but why quibble?)

The new workbooks I create each day typically involve fewer than 100
entries, of which usually fewer than half are formulas. In other words,
I use Excel like an overgrown calculator. If you think Access is
anywhere near as efficient as Excel for this sort of ad hoc
calculation, you're stupider than you've already proven.

>i mean YOU GUYS CREATE THE SAME REPORT EVERY WEEK

No, you create the same report every week. We do real work.

>Excel is dead; and I hope that you Excel dorks are all kicked to the
>curb.. you guys think that you're so good with numbers.. i mean.. WAKE
>UP TO REALITY and either learn Crystal Reports of Microsoft Access.

What makes you think we don't already know these other systems? What
makes you think we don't use them when *WE* believe they're more
appropriate? We just don't use them to the exclusion of anything else,
which seems to be your (so-called) mindset.

>EXCEL SHOULDN'T BE USED FOR DATA ENTRY YOU DORK

To some extent you have a point. Excel isn't ideal, and I don't use it
all that much except for small datasets (<100 entries) or when I have
to enter tabular data I receive in hardcopy (depressingly still not an
infrequent situation - we have a scanner in my department, but no OCR
software - sigh). When possible, I use awk scripts to parse text files,
even text files generated from PDF files.

However, most people don't know scripting languages, so they're stuck
using the tools *THEY* find easiest to use. The world has voted: Excel
is easier to use for data entry. Live with it!

>Excel doesn't store data; it stores spaghetti code and millions of
>different functions

If used in an undisciplined way, true. If used properly, false.

There are a lot of people who claim programming in Assembler (or C) is
pure evil, but Assembler (and C) can do more than anything else. They
can be used badly, but they can also be used well. It takes a lot more
discipline to use them when than, say, VB, but once it becomes a habit,
they're easier to use. Same with spreadsheets vs databases for the
things the rest of us understand are easier to do in spreadsheets. You
don't have a clue what those things are because you only seem to know
how to generate reports. Report generation really isn't the only thing
people use computers to do.

>aren't you tired of changing formulas in hundreds of different cells?

Depends. But on the other hand I'll take formulas over stored query
results when typos or other errors are discovered in some base table
that require hundreds of queries to be rerun.

>there is a better way; it is called ACCESS or CRYSTAL REPORTS.

For generating reports, agreed. For doing real work, you're full of it.

Bob Phillips wrote:
> <aaron.kempf@gmail.com> wrote in message
> news:1120752903.274079.281500@g49g2000cwa.googlegroups.com...
>
> > all of your enterprise level apps should be written in ADP so that you
> > can make changes when you need to.. buying a closed system like SAP and
> > Peoplesoft.. i mean..
>
> You have out-done yourself with asinine comments with this one.

It is worse than you think.

Search for Aaron's posts in the database groups. It appears that even
there he is not very capable. Even when the rants are ignored, his
replies get shot down.

My guess is that he is nowhere near as proficient with his stated
software preferences as he claims.

I don't see him doing well in public relations, either.

Bye,
Jay

Jay Petrulis wrote...
....
>I don't see him doing well in public relations, either.

Yeah, but could Aaron and I revive Point-Counterpoint?

Point - Dull thud maybe <g>

Bob

And that is he not you sir!

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1120759448.605298.156760@g44g2000cwa.googlegroups.com...
> Jay Petrulis wrote...
> ...
> >I don't see him doing well in public relations, either.
>
> Yeah, but could Aaron and I revive Point-Counterpoint?
>

are you DRUNK?
why dont you just keep your reports in a database and then you can USE
IT in 100 different places without troubleshooting AWK and PDF

C is easier to use than VB? You might mean easier-- like it takes you
less effort to write the same structures week in and week out; but VB
is the most productive environment in the world.

you idiot.

Report Generation is the only thing that Excel dorks do.

aaron.kempf@gmail.com wrote...
>are you DRUNK?

No. Are you terminally stupid? Ah, well, not terminally so.

>why dont you just keep your reports in a database and then you can USE
>IT in 100 different places without troubleshooting AWK and PDF

Idiot! How many times do I need to repeat that most of the data I work
with comes from CUSTOMERS! That is, it ain't anywhere in any of my
company's systems other than the e-mail servers to start with, then on
my local drives when I detach files from e-mail, and it isn't even
possible to import it into anything (even dbms's) until it's been
parsed into the equivalent of CSV format.

YOU! may have all the data YOU!'re allowed to use in a nice, neat
database. Ain't so for many, many business users. But since you've
demonstrated that you're incapable of anyone doing any tasks that
you've never done yourself, you're incapable of understanding this.

You live in a dream world in which all data exists on your very own
dbms. The rest of us inhabit the real world.

>C is easier to use than VB? You might mean easier-- like it takes you
>less effort to write the same structures week in and week out; but VB
>is the most productive environment in the world.

Not if you have an existing library of reusable code. Your same
argument was made 20 years ago that TurboPascal was easier to learn and
more productive than C. Where's TurboPascal now? It's morphed into
Delphi, but it hasn't taken over the world.

Am I saying that C is the ideal language for ad hoc application
development? No. But IMO VB isn't ideal either. I'll admit I don't
write much UI code, and I don't have (so don't use) VB proper (though I
did have & use VB3 way back in the early 1990s), but I prefer Perl/Tk
when I need a graphical interface to initiate essentially batch
processes. Unlike you, I'll admit my preference is subjective and
reflects a mindset based on originally learning programming under Unix.

All I'll say for VB is that it allows mediocre programmers to make
multimegabyte GUI applications instead of multi-KB batch applications.
If you think that's productivity, that's your problem.

>Report Generation is the only thing that Excel dorks do.

No, it's apparently the only thing YOU! do (and it's becoming apparent
it may be the only thing you're capable of doing outside the bathroom).

Use a program called Data Junction

aaron.kempf@gmail.com wrote...
>it's not complex. the relational model is the simplest thing in the
>world.
....

If one thinks in terms of table-based relations, perhaps. Most people
don't think that way, so wrong in general.

>The value of peoplesoft and sap is that it is DATABASE DRIVEN.
>These apps dont run on spreadsheets.

It's not that they're essentially database applications, their main
value comes from the relations and tables they provide out-of-the-box.
Now, the things most companies do with them are best done using
databases, so no prizes for PeopleSoft (or Oracle) or SAP that they're
implemented using dbms's.

>Why would you use crayons to draw on pretty reports?
>You're doing the same thing by bringing data into Excel and then you
>sit there-- and you have a copy of the same formula in 100 different
>places.

I have thousands of archived e-mails with a lot of common text, too.
Some redundancy isn't worth the effort to eliminate.

One big advantage of that redundancy is that a given workbook can be
opened and used on any PC or Mac or Linux or BSD box running Excel or
OpenOffice Calc (OK, OOo Calc chokes on some XL functions and many XL
array formulas). I don't need to haul around terabytes of dbms store,
and I don't need a connection to the dbms. And I can write more
formulas if needed, so XLS files are much more useful than static SNP
or PDF files.

Since all you do is create reports, I guess you can't even imagine
needing different perspectives on the same data.

>It is impossible to check all of these formulas and have any confidence
>in your numbers.

Maybe it's impossible for you.

It's a matter of scale. Database queries have formulas, especially ones
using multidimensional analysis and OLAP facilities. Are those formulas
impossible to verify?

A single spreadsheet formula is also easy to verify, and (I may lose
you here because this will take some wit to understand) if one uses
standard software engineering techniques, it's possible to print
formula listings from spreadsheets (R1C1 addressing much better than A1
for this) to text files then use textual pattern matching to locate
anomalies. I've been doing this for almost 2 decades.

Now most spreadsheet users wouldn't recognize a regular expression if
it danced ***** in front of them (stealing shamelessly from J K
Rowlings), but that doesn't imply it's not possible for ANY spreadsheet
user (or developer) to verify their models. Your argument is a vacuous
as saying that since it's difficult to fully verify source code in
large software systems (e.g., dbms's), you can't have any confidence in
the results generated by such systems. Untrue. There's empirical
confidence. Has it worked well in the past? Does it handle test cases
correctly? But you wouldn't understand that unless some dbms spit it
out at you in a report simple enough for you to understand.

>I can do anything with SQL Server that you can do with Excel. And I
>can do it faster, against more than 65k records.

OK, let's see whether (i.e., prove) you're talking out your backside.

Use a nice outside data source like the U.S. Bureau of Labor
Statistics.

http://data.bls.gov/cgi-bin/surveymost?ce

Pull 'Total Private Average Weekly Earnings, 1982 Dollars - Seasonally
Adjusted - CES0500000051' by checking its check box and clicking on the
'Retrieve Data' button at the bottom of the web form.

On the next web form, click the 'More Formatting Options' link. In the
next form, choose 'Column Format' from the list under 'Select view of
the data' and 'Text' under 'Output type' (choose whatever plain text
format you'd prefer - I use comma delimited). Under 'Select the time
frame for your data', specify years 1985 to 2005. Then click the
'Retrieve Data' button. This should give you a table with field names
and 245 records.

Pull that data into your dbms, but let us know in detail all the steps
you need to do that. The following steps are all that's needed to pull
it into Excel.

1. Select the entire table (field names to bottommost row excluding the
'(p)' at the end of the last field in the bottommost row) and copy it
to the clipboard.
2. Switch to Excel and run the menu command Edit > Paste Special, Text.
3. Immediately after pasting, run the menu command Data > Text to
Columns, choose Delimited and click the Next button, then check 'Comma'
as a delimiter character and click the 'Finish' button.

The data is now ready to use.

Let's do something moderately tricky - pull the largest and average
relative (percentage) increases and decreases in rolling 3 month
average weekly wages. (Yes, you could pull the rolling 3-month figures
from the BLS database, but let's compare Excel vs your database.)

I imported the data into a new worksheet beginning in cell A1, so after
parsing A1 contains 'Series ID'. One approach in Excel.

1. Enter the formula =AVERAGE(D2:D4) in cell E4, then double click on
the Fill Handle to fill it down into E5:E246.
2. Enter the formula =(E5-E4)/E4 in cell F5, then double click on the
Fill Handle to fill it down into F6:F246.
3. Enter the array formula =MAX(IF(F5:F246>0,F5:F246)) in cell H1 to
get the largest rolling 3 month percentage increase.
4. Enter the array formula =MIN(IF(F5:F246<0,F5:F246)) in cell H2 to
get the largest rolling 3 month percentage decrease.
5. Enter the array formula =AVERAGE(IF(F5:F246>0,F5:F246)) in cell H3
to get the average rolling 3 month percentage increase.
6. Enter the array formula =AVERAGE(IF(F5:F246<0,F5:F246)) in cell H3
to get the average rolling 3 month percentage decrease.

For extra points, enter the formula =COUNTIF($F$5:$F$246,H1) in cell I1
and fill it down into cell I2 to get the number of rolling 3 month %
changes matching the largest increase and decrease, respectively, and
enter =INDEX($B$2:$B$246&" "&$C$2:$C$246,MATCH(H1,$F$2:$F$246,0)) in
cell J1 and fill it down into cell J2 to get the year and month
corresponding to the topmost (earliest) periods experiencing the
largest % increase or decrease, respectively.

Now let's make it trickier. Enter 3 in cell G1. Clear columns E and F.
Enter the following formula in E2.

=IF(ROWS(E$2:E2)<$G$1,"",AVERAGE(OFFSET(D2,0,0,-$G$1,1)))

Double click the Fill Handle to fill it down into E3:E246. Enter the
following formula in cell F2.

=IF(COUNT(E1:E2)=2,(E2-E1)/E1,"")

Double click the Fill Handle to fill it down into F3:F246. The cells in
H1:J4 should give the same results as they had before. Now change G1 to
6. The formulas in J1:J4 now return rolling 6 month results.

Try making the number of periods over which to average a simple query
parameter.

>AND YES, I DO MAKE PURCHASING DECISIONS.

What kind of TP, what to have for lunch, . . .

>You're the idiots that are ignorant.
>
>APPS are:
>a) data entry
>b) report generation

Yup. You've confirmed the narrowness of your worldview. I'd suspected
it all along, but it's good to have proof (that is, if someone of your,
er, intellect is competent to make accurate self-assessments).

>Excel can't do either.. so take your spreadsheet and screw yourself

Difference of opinion on (a). What's easiest for manually keying data
is purely subjective. If there would be several users updating the same
file at the same time, then obviously databases would be better.
However, that's not the sort of data entry I do.

As for anything other than data entry or report generation, since your
wee tiny brain seems incapable of imagining any such thing, no point
sayng anything more than YOU'RE TOO STUPID TO UNDERSTAND THEM.