Charles,
In the spirit of debate I'd take issue with some of what you have said.
If you are looking at the mathematical side fundamentally you can't get away
from complex things being based on doing simple things (add, subtract,
multiply, divide etc). The key is in how your data is presented to the
problem.
I accept that if you import a spreadsheet into Access (because it has got
big say) & then try to do funky financial maths on it you are likely to hit a
wall. I see this 'accountants folly' on a daily basis (insistance that all of
the data is in one table & then wondering why the DB performs like a dog & is
chewing up server space at a vast rate of knots). You have to think about the
problem & see how it will work.
Where databases can help is in producing 'audit friendly' intermediate steps
that can be independently validated & repeated. Again it comes down to using
the software properly though.
Regards,
Chris.
--
Chris Marlow
MCSD.NET, Microsoft Office XP Master
"Charles Williams" wrote:
Excel calculates very fast: generally you wont see much improvement in
calculations speed in VB except if you can get to the same result with fewer
calculations.
For information on large Excel workbooks and optimising calculation speed I
suggest you look at my website.
Databases are good at manipulating data, but not much good at doing
calculations:
The problem with SQL-type database calculations is that it is very hard work
to do any kind of complex calculations eg:
- time series calculations
- calculations that reference other rows
- offset calculations (row before or column after kind of thing)
SQL is designed around the idea that all the data you want for the
calculation exists on the row where the result will surface. This makes it
excellent for doing a small number of repeated simple calculations on a
large number of identical rows, but just about impossible for any kind of
model that involves a large number of formulae. So the only answer for these
kinds of calculations in a database is to program the calculations
processing arrays or recordsets in whatever language the DB supports.
So IMHO the best of both worlds is to do the data manipulation in a
database and then calculate the results with a spreadsheet that gets its
data from the DB.
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com
"Chris Marlow" wrote in message
...
Ed,
Thinking on I was probably a bit hasty. But the things that you would do
would cause the spreadsheet to lose its 'nature' as a spreadsheet. By that
I
mean the idea of a spreadsheet is that when you change a cell the changes
propagate throughout the workbook as it recalculates. I'd say you would
have
to 'batch up' some of the tasks your spreadsheet does, thus losing that
flexibility. I see it a lot the 'paste special' crowd who then wonder how
they ever got thier results.
If you had a bit of your spreadsheet where you were, say, heavily using
vlookups to match data then this may be better done in a database (which
after all has a relational engine which tries to optimise how it joins
data).
I'd say data manipulation wise databases can do everything spreadsheets
can.
It is just the display that is different. An issue I faced is where I had
a
financial analytics package that only exposed itself to Excel. If you have
an
issue like that then you are stuck with Excel.
There may well be some things you can do to optimise your formulae, eg
things like VLOOKUP & MATCH can cause issues, I'd google & see if you can
find some FAQ/hints & tips pages.
Regards,
Chris.
--
Chris Marlow
MCSD.NET, Microsoft Office XP Master
"Ed" wrote:
Chris,
That's tough - most of the formulas are seriously complex and although
I'm
no authority on access, I've been told it doesn't have quite the same
level
of functionality as excel. Is there any other way to outsource the
calculations?
Cheers,
Ed.
"Chris Marlow" wrote:
Ed,
Maybe, but probably not. If you have a lot of rows of data leading to
your
results then some preprocessing in a database (Access say) might help.
Regards,
Chris.
--
Chris Marlow
MCSD.NET, Microsoft Office XP Master
"Ed" wrote:
Excel 2000: I'm currently using a spreadsheet with so many formulas
on it
that it's limiting the amount of data I can use it to process. If I
program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?