Date Format Conversion?

I would use a helper column with a formula like:

=RIGHT("0"&A1,8)

Then drag this down all 35000 rows.

Then select that column and do data|text to columns
delimited (by nothing)
and choose mdy as the format

Then you could delete the original column or just paste over it.

===
This looks like it will work since your dates are formatted as mddyyyy or
mmddyyyy.

Chris in Nebraska wrote:
>
> I have a tab delimited text file with a column of dates. I copied and
> pasted a selection of them below as a reference, there are in reality
> over 35,000 rows of these dates.
>
> These text dates must be converted to the following date format and
> saved back into a text file as such:
>
> mm/dd/yyyy
>
> That includes leading zeroes in months and dates. E.g. 01/01/1999
>
> To complicate matters, I need to explain the process to others.
>
> Does anyone even know where I should begin?
>
> THANK YOU,
> - Chris
>
> +++++++++++++++++++++++++++
>
> 9031989
> 8131990
> 9201989
> 1271990
> 10291989
> 1081990
> 3041990
> 10051989
> 4191990
> 10171989
> 4241990
> 12181989
> 11061989
> 8291989
> 11021989
> 12291989
> 10181989
> 5151990
> 11221989
> 9111989
> 9111989
> 5231990
> 11211989
> 7271989
> 10301989
> 5241990
> 6081990
> 9211989
> 10021990

--

Dave Peterson

I have a tab delimited text file with a column of dates. I copied and
pasted a selection of them below as a reference, there are in reality
over 35,000 rows of these dates.

These text dates must be converted to the following date format and
saved back into a text file as such:

mm/dd/yyyy

That includes leading zeroes in months and dates. E.g. 01/01/1999

To complicate matters, I need to explain the process to others.

Does anyone even know where I should begin?

THANK YOU,
- Chris

+++++++++++++++++++++++++++


9031989
8131990
9201989
1271990
10291989
1081990
3041990
10051989
4191990
10171989
4241990
12181989
11061989
8291989
11021989
12291989
10181989
5151990
11221989
9111989
9111989
5231990
11211989
7271989
10301989
5241990
6081990
9211989
10021990

Dave -

The helper column solves my first issue - the lack of leading zeros in
months.

I don't understand the "Text to columns" piece yet. I follow the steps
and nothing happens. What SHOULD happen when I do that?

Thanks a *MILLION* for your assistance, by the way!

- Chris


Dave Peterson wrote:
> I would use a helper column with a formula like:
>
> =RIGHT("0"&A1,8)
>
> Then drag this down all 35000 rows.
>
> Then select that column and do data|text to columns
> delimited (by nothing)
> and choose mdy as the format
>
> Then you could delete the original column or just paste over it.
>
> ===
> This looks like it will work since your dates are formatted as mddyyyy or
> mmddyyyy.
>
> Chris in Nebraska wrote:
>
>>I have a tab delimited text file with a column of dates. I copied and
>>pasted a selection of them below as a reference, there are in reality
>>over 35,000 rows of these dates.
>>
>>These text dates must be converted to the following date format and
>>saved back into a text file as such:
>>
>>mm/dd/yyyy
>>
>>That includes leading zeroes in months and dates. E.g. 01/01/1999
>>
>>To complicate matters, I need to explain the process to others.
>>
>>Does anyone even know where I should begin?
>>
>>THANK YOU,
>>- Chris
>>
>>+++++++++++++++++++++++++++
>>
>>9031989
>>8131990
>>9201989
>>1271990
>>10291989
>>1081990
>>3041990
>>10051989
>>4191990
>>10171989
>>4241990
>>12181989
>>11061989
>>8291989
>>11021989
>>12291989
>>10181989
>>5151990
>>11221989
>>9111989
>>9111989
>>5231990
>>11211989
>>7271989
>>10301989
>>5241990
>>6081990
>>9211989
>>10021990
>
>

Chris,

Excel doesn't see these as bona fide dates. Thus, you can't change the date
format. In a helper column, you could use:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

copied down with the fill handle. This will give bona fide dates, so the
date format can bechanged with Format - Cells - Number - Date. Or use
Format - Number - Date - Custom, and roll your own format codes, mm/dd/yyyy
for your requirement.

Once this column is working, to write it to a text file, you could move
(Cut/Paste) the helper column to the first column of another sheet. Now
save that sheet as a text file.

If you'll be doing this regularly, you might want to use Data - Get external
data, rather than File - Open. That will allow you to keep reading your
text file into an existing workbook already set up with this stuff. That's
importing, rather than opening, a text file. More at
http://www.smokeylake.com/excel/textfiles.htm.
--
Earl Kiosterud
www.smokeylake.com

"Chris in Nebraska" <cpultz2@gmail.com> wrote in message
news:%23ac1cDpzFHA.164@TK2MSFTNGP10.phx.gbl...
>
> I have a tab delimited text file with a column of dates. I copied and
> pasted a selection of them below as a reference, there are in reality over
> 35,000 rows of these dates.
>
> These text dates must be converted to the following date format and saved
> back into a text file as such:
>
> mm/dd/yyyy
>
> That includes leading zeroes in months and dates. E.g. 01/01/1999
>
> To complicate matters, I need to explain the process to others.
>
> Does anyone even know where I should begin?
>
> THANK YOU,
> - Chris
>
> +++++++++++++++++++++++++++
>
>
> 9031989
> 8131990
> 9201989
> 1271990
> 10291989
> 1081990
> 3041990
> 10051989
> 4191990
> 10171989
> 4241990
> 12181989
> 11061989
> 8291989
> 11021989
> 12291989
> 10181989
> 5151990
> 11221989
> 9111989
> 9111989
> 5231990
> 11211989
> 7271989
> 10301989
> 5241990
> 6081990
> 9211989
> 10021990
>
>

***** Earl - MANY THANKS! *****

I believe the formula you have here solves my issue. I don't need (or
actually WANT) the cells to have an Excel date format really, it is
being saved out to text again anyway.

You saved the day!!

- chris



Earl Kiosterud wrote:
> Chris,
>
> Excel doesn't see these as bona fide dates. Thus, you can't change the date
> format. In a helper column, you could use:
>
> =DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
>
> copied down with the fill handle. This will give bona fide dates, so the
> date format can bechanged with Format - Cells - Number - Date. Or use
> Format - Number - Date - Custom, and roll your own format codes, mm/dd/yyyy
> for your requirement.
>
> Once this column is working, to write it to a text file, you could move
> (Cut/Paste) the helper column to the first column of another sheet. Now
> save that sheet as a text file.
>
> If you'll be doing this regularly, you might want to use Data - Get external
> data, rather than File - Open. That will allow you to keep reading your
> text file into an existing workbook already set up with this stuff. That's
> importing, rather than opening, a text file. More at
> http://www.smokeylake.com/excel/textfiles.htm.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Chris in Nebraska" <cpultz2@gmail.com> wrote in message
> news:%23ac1cDpzFHA.164@TK2MSFTNGP10.phx.gbl...
>
>>I have a tab delimited text file with a column of dates. I copied and
>>pasted a selection of them below as a reference, there are in reality over
>>35,000 rows of these dates.
>>
>>These text dates must be converted to the following date format and saved
>>back into a text file as such:
>>
>>mm/dd/yyyy
>>
>>That includes leading zeroes in months and dates. E.g. 01/01/1999
>>
>>To complicate matters, I need to explain the process to others.
>>
>>Does anyone even know where I should begin?
>>
>>THANK YOU,
>>- Chris
>>
>>+++++++++++++++++++++++++++
>>
>>
>>9031989
>>8131990
>>9201989
>>1271990
>>10291989
>>1081990
>>3041990
>>10051989
>>4191990
>>10171989
>>4241990
>>12181989
>>11061989
>>8291989
>>11021989
>>12291989
>>10181989
>>5151990
>>11221989
>>9111989
>>9111989
>>5231990
>>11211989
>>7271989
>>10301989
>>5241990
>>6081990
>>9211989
>>10021990
>>
>>
>
>
>

What should have happened is that excel would have converted those text dates
(09302001) into a real date.

You may want to try it again.

Chris in Nebraska wrote:
>
> Dave -
>
> The helper column solves my first issue - the lack of leading zeros in
> months.
>
> I don't understand the "Text to columns" piece yet. I follow the steps
> and nothing happens. What SHOULD happen when I do that?
>
> Thanks a *MILLION* for your assistance, by the way!
>
> - Chris
>
> Dave Peterson wrote:
> > I would use a helper column with a formula like:
> >
> > =RIGHT("0"&A1,8)
> >
> > Then drag this down all 35000 rows.
> >
> > Then select that column and do data|text to columns
> > delimited (by nothing)
> > and choose mdy as the format
> >
> > Then you could delete the original column or just paste over it.
> >
> > ===
> > This looks like it will work since your dates are formatted as mddyyyy or
> > mmddyyyy.
> >
> > Chris in Nebraska wrote:
> >
> >>I have a tab delimited text file with a column of dates. I copied and
> >>pasted a selection of them below as a reference, there are in reality
> >>over 35,000 rows of these dates.
> >>
> >>These text dates must be converted to the following date format and
> >>saved back into a text file as such:
> >>
> >>mm/dd/yyyy
> >>
> >>That includes leading zeroes in months and dates. E.g. 01/01/1999
> >>
> >>To complicate matters, I need to explain the process to others.
> >>
> >>Does anyone even know where I should begin?
> >>
> >>THANK YOU,
> >>- Chris
> >>
> >>+++++++++++++++++++++++++++
> >>
> >>9031989
> >>8131990
> >>9201989
> >>1271990
> >>10291989
> >>1081990
> >>3041990
> >>10051989
> >>4191990
> >>10171989
> >>4241990
> >>12181989
> >>11061989
> >>8291989
> >>11021989
> >>12291989
> >>10181989
> >>5151990
> >>11221989
> >>9111989
> >>9111989
> >>5231990
> >>11211989
> >>7271989
> >>10301989
> >>5241990
> >>6081990
> >>9211989
> >>10021990
> >
> >

--

Dave Peterson

On Tue, 11 Oct 2005 13:23:20 -0500, Chris in Nebraska <cpultz2@gmail.com>
wrote:

>
>I have a tab delimited text file with a column of dates. I copied and
>pasted a selection of them below as a reference, there are in reality
>over 35,000 rows of these dates.
>
>These text dates must be converted to the following date format and
>saved back into a text file as such:
>
>mm/dd/yyyy
>
>That includes leading zeroes in months and dates. E.g. 01/01/1999
>
>To complicate matters, I need to explain the process to others.
>
>Does anyone even know where I should begin?
>
>THANK YOU,
>- Chris
>
>+++++++++++++++++++++++++++
>
>
>9031989
>8131990
>9201989
>1271990
>10291989
>1081990
>3041990
>10051989
>4191990
>10171989
>4241990
>12181989
>11061989
>8291989
>11021989
>12291989
>10181989
>5151990
>11221989
>9111989
>9111989
>5231990
>11211989
>7271989
>10301989
>5241990
>6081990
>9211989
>10021990
>

If your data is in A1:A35000

B1: =TEXT(A1,"00\/00\/0000")

Fill down to B35000

Then select column B
Edit/Copy
Edit/Paste Special/Values

Delete Column A

--ron

And this works very nicely since the OP wanted to save the data back to a text
file.

But just some thoughts...

If the OP wanted a real date, then a minor modification to Ron's formula may
work:

=--TEXT(A1,"00\/00\/0000")
Format as a date

But this will fail if the windows regional settings for the short date format is
different than the order of the digits in that cell. (Bad sentence!)

If the numbers are mmddyyyy and the regional setting is set for mm/dd/yyyy, then
it works fine.

But if the regional settings for the short date is dd/mm/yyyy, then this will
fail.

(Just a warning that doesn't apply in this case <bg>)

Ron Rosenfeld wrote:
>
> On Tue, 11 Oct 2005 13:23:20 -0500, Chris in Nebraska <cpultz2@gmail.com>
> wrote:
>
> >
> >I have a tab delimited text file with a column of dates. I copied and
> >pasted a selection of them below as a reference, there are in reality
> >over 35,000 rows of these dates.
> >
> >These text dates must be converted to the following date format and
> >saved back into a text file as such:
> >
> >mm/dd/yyyy
> >
> >That includes leading zeroes in months and dates. E.g. 01/01/1999
> >
> >To complicate matters, I need to explain the process to others.
> >
> >Does anyone even know where I should begin?
> >
> >THANK YOU,
> >- Chris
> >
> >+++++++++++++++++++++++++++
> >
> >
> >9031989
> >8131990
> >9201989
> >1271990
> >10291989
> >1081990
> >3041990
> >10051989
> >4191990
> >10171989
> >4241990
> >12181989
> >11061989
> >8291989
> >11021989
> >12291989
> >10181989
> >5151990
> >11221989
> >9111989
> >9111989
> >5231990
> >11211989
> >7271989
> >10301989
> >5241990
> >6081990
> >9211989
> >10021990
> >
>
> If your data is in A1:A35000
>
> B1: =TEXT(A1,"00\/00\/0000")
>
> Fill down to B35000
>
> Then select column B
> Edit/Copy
> Edit/Paste Special/Values
>
> Delete Column A
>
> --ron

--

Dave Peterson

Chris,

The formula converts it to Excel date format. Having it in date format
allows you to change the date formatting (Format - Cells ...), which you
said you want. When the sheet is saved to a txt file, it should be exactly
as date-formatted.
--
Earl Kiosterud
www.smokeylake.com

"Chris in Nebraska" <cpultz2@gmail.com> wrote in message
news:OGofAWqzFHA.4032@TK2MSFTNGP15.phx.gbl...
> ***** Earl - MANY THANKS! *****
>
> I believe the formula you have here solves my issue. I don't need (or
> actually WANT) the cells to have an Excel date format really, it is being
> saved out to text again anyway.
>
> You saved the day!!
>
> - chris
>
>
>
> Earl Kiosterud wrote:
>> Chris,
>>
>> Excel doesn't see these as bona fide dates. Thus, you can't change the
>> date format. In a helper column, you could use:
>>
>> =DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
>>
>> copied down with the fill handle. This will give bona fide dates, so the
>> date format can bechanged with Format - Cells - Number - Date. Or use
>> Format - Number - Date - Custom, and roll your own format codes,
>> mm/dd/yyyy for your requirement.
>>
>> Once this column is working, to write it to a text file, you could move
>> (Cut/Paste) the helper column to the first column of another sheet. Now
>> save that sheet as a text file.
>>
>> If you'll be doing this regularly, you might want to use Data - Get
>> external data, rather than File - Open. That will allow you to keep
>> reading your text file into an existing workbook already set up with this
>> stuff. That's importing, rather than opening, a text file. More at
>> http://www.smokeylake.com/excel/textfiles.htm.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "Chris in Nebraska" <cpultz2@gmail.com> wrote in message
>> news:%23ac1cDpzFHA.164@TK2MSFTNGP10.phx.gbl...
>>
>>>I have a tab delimited text file with a column of dates. I copied and
>>>pasted a selection of them below as a reference, there are in reality
>>>over 35,000 rows of these dates.
>>>
>>>These text dates must be converted to the following date format and saved
>>>back into a text file as such:
>>>
>>>mm/dd/yyyy
>>>
>>>That includes leading zeroes in months and dates. E.g. 01/01/1999
>>>
>>>To complicate matters, I need to explain the process to others.
>>>
>>>Does anyone even know where I should begin?
>>>
>>>THANK YOU,
>>>- Chris
>>>
>>>+++++++++++++++++++++++++++
>>>
>>>
>>>9031989
>>>8131990
>>>9201989
>>>1271990
>>>10291989
>>>1081990
>>>3041990
>>>10051989
>>>4191990
>>>10171989
>>>4241990
>>>12181989
>>>11061989
>>>8291989
>>>11021989
>>>12291989
>>>10181989
>>>5151990
>>>11221989
>>>9111989
>>>9111989
>>>5231990
>>>11211989
>>>7271989
>>>10301989
>>>5241990
>>>6081990
>>>9211989
>>>10021990
>>>
>>>
>>
>>

excel is a disease because it really doesn't understand datatypes.

i would reccomend uninstalling excel from every machine in the world
and starting over with Access or Crystal Reports.

that way; you can build a report ONCE and run it with parameters;
instead of having to copy and paste 100 different copies of the same
data lol

aaron.kempf@gmail.com wrote...
>excel is a disease because it really doesn't understand datatypes.
>
>i would reccomend uninstalling excel from every machine in the world
>and starting over with Access or Crystal Reports.
>
>that way; you can build a report ONCE and run it with parameters;
>instead of having to copy and paste 100 different copies of the same
>data lol

You just won't be able to calculate much. Too bad if that's what you
really need to do.

Also typical that you either didn't read or didn't understand the OP.
The issue is import and parsing, not storage. Data type conversion is
the issue, and Access is no walk on the beach converting 8 sequential
decimal digits into date fields when importing tables from text files.

Dave Peterson wrote...
....
>But this will fail if the windows regional settings for the short date format is
>different than the order of the digits in that cell. (Bad sentence!)
>
>If the numbers are mmddyyyy and the regional setting is set for mm/dd/yyyy, then
>it works fine.
>
>But if the regional settings for the short date is dd/mm/yyyy, then this will
>fail.
....

But your suggestion of using Data > Text to columns would fail if dates
were in ddmmyyyy format and you parsed them as mdy.

Moral: there's no universally correct way to parse dates formatted as 8
decimal digits in sequence. There are different answers for mmddyyy,
ddmmyyyy and yyyymmdd. You could get fancy and adapt your formula based
on trying to convert a random sample using each format, but that seems
overkill.

But in this case, the OP did want mdy. If the data were in some other order,
then the user could chose that order from the dropdown for the date option.

I guess I have a little expectation that the user will be able to choose the
correct sequence to match their data <bg>.

Harlan Grove wrote:
>
> Dave Peterson wrote...
> ...
> >But this will fail if the windows regional settings for the short date format is
> >different than the order of the digits in that cell. (Bad sentence!)
> >
> >If the numbers are mmddyyyy and the regional setting is set for mm/dd/yyyy, then
> >it works fine.
> >
> >But if the regional settings for the short date is dd/mm/yyyy, then this will
> >fail.
> ...
>
> But your suggestion of using Data > Text to columns would fail if dates
> were in ddmmyyyy format and you parsed them as mdy.
>
> Moral: there's no universally correct way to parse dates formatted as 8
> decimal digits in sequence. There are different answers for mmddyyy,
> ddmmyyyy and yyyymmdd. You could get fancy and adapt your formula based
> on trying to convert a random sample using each format, but that seems
> overkill.

--

Dave Peterson

what.. in Access dont i just use CDATE

because listen-- there are DATA TYPES IN ACCESS AND EXCEL DOESNT HAVE
STRONG ENOUGH VALIDATION TO DO JACK ****.

You can have 'DATE FIELDS' in a database.

In excel, you can't really even enforce that a certain cell is a date,
can you?

i mean-- cut and paste gets around protection and validation right?

i just think that it's hilarious-- you kids can keep on making $12/hour
and answering phone calls at lunch

LEARNING DATABASES DOESN'T MAKE YOU A DORK.

Don't be afraid of them; take a couple of classes; read a couple of
books. As it is you guys spend half your day MAKING THE SAME DAMN
SPREADSHEET WEEK IN AND WEEK OUT

Do you really think that is sustainable?

My mission in life is to put spreadsheet dorks out of a job. You guys
are swimming in a cesspool from 1994

-Aaron

aaron.kempf@gmail.com wrote...
>what.. in Access dont i just use CDATE
....

Dunno. Do you?

I'll give Access this: when I open a CSV file, it brings up its parsing
wizard. If only Excel would.

>In excel, you can't really even enforce that a certain cell is a date,
>can you?

You can, but it requires programming. Ideally, add the cells to a range
defined with a particular name, then use Change and Calculate event
handlers to watch the cells' contents. Data Validation is easily
compromised.

That said, the ability of different cells to hold entries of different
type is one of Excel's strengths. Can it be misused? Sure. But it
allows for flexibility databases lack.

>LEARNING DATABASES DOESN'T MAKE YOU A DORK.

No. As you prove, it comes naturally to some.

>My mission in life is to put spreadsheet dorks out of a job. . . .

So you want to be as unsuccessful as you are pathetic?

technically-- even if you program event handlers to say 'make sure
column F is a date' you can still disable macros and go in and freely
type in whatever you want

im not unsuccessful.

I have offers coming out of my ears for $60-$70/hour. That is MY
rate-- that is what I take home.

Doing little cheesy Access stuff.

I mean jesus-- you spreadsheet dorks really need to wake up to the 21st
century.

aaron.kempf@gmail.com wrote...
>technically-- even if you program event handlers to say 'make sure
>column F is a date' you can still disable macros and go in and freely
>type in whatever you want

You can. You can discourage users from trying to use workbooks with
macros disabled by using do-nothing udf in key formulas. If macros are
disabled, so are udfs, in which case udfs return #NAME? errors which
propagate through downstream formulas.

>I mean jesus-- you spreadsheet dorks really need to wake up to the 21st
>century.

You still don't get the main difference between you and most
spreadsheet users. You're paid specifically for database development.
Most spreadsheet users are paid to do things like financial analysis or
marketing studies, and spreadsheets are just one of many tools used.
Databases may be the be-all and end-all of your job(s), but
spreadsheets aren't the central focus of most spreadsheet users' jobs.

When are you going to wake up and realize there are people with jobs
outside IT departments who nevertheless use computers in their jobs?

UDFs run on SQL Server. I do **** like that on a DATABASE SERVER not
on the client.

I am paid to build reports.

you guys dont actually DO jack **** you just drink your foo-foo drinks
and wear your turtlenecks and dont get jack **** done

spreadsheets ARE the central focus of most spreadsheet dorks' job.. i
mean YOU SPEND ALL WEEK BUILDING THE SAME GODDAMN REPORT WEEK IN AND
WEEK OUT

go out and learn crystal or somethign and STFU

and just for the record; i almost NEVER work in IT departments. I work
for 'real world wimps' like you that spend 10 hours a week building the
same goddamn spreadsheets. I mean-- it's ******* disgusting that you
get a single penny for a paycheck.

Financial Analysis? Marketing Studies?

it's all numbers buddy. I mean seriously; do you think that you caress
the numbers better than I do?

oh, nice numbers... nice numbers

grow up.. you are a 'SPREADSHEET DEVELOPER'-- you might as well be
developing in something THAT MAKES SENSE.

from a logistical standpoint-- YOU BUILD THE SAME GODDAMN REPORT WEEK
IN AND WEEK OUT

DO YOU REALLY THINK THAT CRYSTAL REPORTS WON'T REPLACE YOUR JOB?

get a real job spreadsheet kids

and just for the record.. flexibility isn't a good thing when you have
to copy the same goddamn formula to 20,000 different cells.

flexibility isn't a good thing when you have 10 copies of the same
data; all sorted in different ways.

all it causes is BLOAT.


oh, im sorry.. let me wait 10 minutes for this attachment to download.
And then let me forward it to johnnie; save it to a network drive..
make a couple of backup copies

I MEAN-- COME ON KEEP YOUR DATA IN A DATABASE AND SHOVE EXCEL UP YOUR
***

degrade performance?

the only people that have performance problems (im not talking abotu
sexual performance problems lol) are spreadsheet people.

because you guys use the wrong tool for 80% of the stuff that you do.

and just for the record, I dont get specs I get problems and I fix
them. Most of the time i get called in 'to fix a little excel problem'
and we end up converting everythign to a database.. so that multiple
people can use the numbers at the same time.

I'm just curious. You claim you dont make reports.. so uh.. what VALUE
do you bring to a company?

You really think that making the same damn spreadsheet 3 times a week
means that you have special numbers that can't possibly be replaced by
crystal??

-Aaron

aaron.kempf@gmail.com wrote...
>degrade performance?
....

Yes, degrade server performance. If you have a udf used by one user of,
say, 100 uers, and that udf performs 100 million floating point
operations (not unreasonable for a serious similation analysis, noting
that each similation iteration could involve dozens of FLOPs), either
that user's apparent performance will be worse if the udf runs on the
server in 1% of the server's user time slices or everyone else's
performance will suffer if this one user's udf gets any sort of
priority.

No matter how wonderful you believe database may be, the processors on
which they run can only handle one operation at a time.

>and just for the record, I dont get specs I get problems and I fix
>them. Most of the time i get called in 'to fix a little excel problem'
>and we end up converting everythign to a database.. so that multiple
>people can use the numbers at the same time.

For applications that multiple people would use, that's a sensible
thing to do. For applications that only one person at a time would use,
that's a waste of resources.

>I'm just curious. You claim you dont make reports.. so uh.. what VALUE
>do you bring to a company?

I'll let you ponder that. Hint: some people at Ford Motors design cars
rather than write reports - do they bring any value? Some people at
financial services companies hedge currency exchange risk and don't
write reports themselves (they delegate such tasks) - do they bring any
value?

You really don't understand that most companies mainly produce things
other than reports, don't you? Very few businesses make their money by
selling reports to customers, and only a subset of them sell reports
that could be fully generated automatically by databases. Maybe you've
only worked for overhead departments that most produce reports. Sad,
but a lack of perspective seems to be one of your manifold
deficiencies.

>You really think that making the same damn spreadsheet 3 times a week
>means that you have special numbers that can't possibly be replaced by
>crystal??

The same spreadsheet? As I've said before in other threads, I use
common templates. To the extent that the formulas are the same in each
workbook produced from these templates, there's storage redundancy. Is
that bad? Depends. It means I can work on these files offline, and it
means others can load them without having to add database software and
settings which aren't standard where I work.

One big advantage of this is that I can check the formulas actually
used in each workbook. If the financial details of a particular
contract were based on faulty formulas, it's necessary to recalculate
the numbers using the corrected formulas, but it's also necessary to
retain the original calculations. It gets messy keeping multiple
versions of canned queries and reports to produce results using
incorrect and corrected calculations.

Harlan

I'm not saying the companies dont add values.

I'm saying that you spreadsheet idiots need to get a real program.
I mean-- you guys are 'scared to be seen as a software developer' but
all you do is sit around and create the same friggin spreadsheet week
in and week out.

I just think that it's a load of crap that you guys sit around loafing
while we get all the work done.

And 100m repetitions of a function?

I'd MUCH rather do that on a database server than in Excel lol
It's faster in TSQL than anywhere else.




You can CHECK the formulas used in each worksheet?

YOU CAN PERSONALLY CHECK 65k formulas times 100 sheets times 100
columns?

that's my big beef with excel is that you have 10 billion DIFFERENT
copies of the same formula in order to get anythign done.

If i could PIN a column to a certain formula-- without copying and
pasting that formula 65k times-- then i would be fine with letting
Excel reside on my machine.

You sit there and say 'mulitple copies of canned queries and reports'--
for starters if you have a NAMING CONVENTION it isn't a problem.

You use common templates; so you take your little XLS and create it
from an XLT.. and then you have 20 copies of that XLS--- so that when
you need to change the logic in your XLT then you have to copy it into
20 different worksheets?

ARE YOU FOR REAL?

DO PEOPLE REALLY PAY YOU TO MAKE SUCH BAD DECISIONS??

I've worked for a lot of different departments. And every time I see
an overpaid Excel dork that makes the same friggin spreadsheet every
week-- it just makes me sick.

you guys deserve to be homeless; living on the streets-- for you are
not adapting to technology.

Time to lose the training wheels kids

and oh yeah-- all your worry about 'database settigns and software' and
all that other crap. I dont do 'dsns or any custom software'.

I DO ACCESS. IT COMES WITH OFFICE, REMEMBER?

aaron.kempf@gmail.com wrote...
....
>I just think that it's a load of crap that you guys sit around loafing
>while we get all the work done.
....

By your own admission, you only help others produce reports. That's
overhead. Do you and your ilk burn through SG&A expense? Sure, but
that's not the same as getting any valuable work done.

>And 100m repetitions of a function?

No, bozo, 100 million floating point operations (FLOPs). Since you know
so little about mathematical programming, it's no surprise you have
difficulty with this.

>I'd MUCH rather do that on a database server than in Excel lol
>It's faster in TSQL than anywhere else.

I'd suspect optimized FORTRAN or C would be faster still.

>You can CHECK the formulas used in each worksheet?
>
>YOU CAN PERSONALLY CHECK 65k formulas times 100 sheets times 100
>columns?

If I were stupid enough to build workbooks that large, yes, I could
still check them. Print the formulas to text files in R1C1 address
format, then use a scripting language to collect cells containing the
same formula text in R1C1 address format (there'll be far fewer than in
A1 address format), then use a procedure to accumulate those cells into
rectangular blocks of cells. Produce a (WTH) report showing formula
text followed by an indented list of all rectangular blocks containing
that formula. Check for inconsistencies. And just a simple fc (that's a
console mode program that comes with Windows) call to check that the
formulas in two different workbooks are the same or show where they
differ.

It does help to know which are the best tools to use for any given
task. clearly you have no clue how to check spreadsheets.

>You sit there and say 'mulitple copies of canned queries and reports'--
>for starters if you have a NAMING CONVENTION it isn't a problem.

You just have to remember which name to use. I'm not saying it's
impossible to reproduce older versions of reports in DBMSs, just that
it's messy.

>You use common templates; so you take your little XLS and create it
>from an XLT.. and then you have 20 copies of that XLS--- so that when
>you need to change the logic in your XLT then you have to copy it into
>20 different worksheets?
....

Only when it makes sense to change the logic. Did you fail to grasp the
need to keep how calculations were actually performed? For deals
already on the books, you wouldn't change the existing XLS files. You'd
just use their data in the new template. And just because you can't
figure out how to use macros to enter formulas in New.XLS of the form

SheetX!Y99:
='X:\Y\Z\[Old.XLS]SheetX'!Y99

in order to automate loading data from one workbook into another
doesn't mean Excel can't. Hint: it requires iterating through
worksheets and unprotected cells in the used ranges of those
worksheets. Not very difficult, but perhaps beyond your skill set.

>I DO ACCESS. IT COMES WITH OFFICE, REMEMBER?

No, it comes with Office *PROFESSIONAL*. It's as absent as your wit in
Office Standard. As I've pointed out before, in the department in which
I work (22 people currently), I'm the *only* one with Access. How does
a business PC user use Access if they don't have it? If they have *ANY*
version of Office, they have Excel. The same isn't true for Access. You
continue to fail to understand that.

scripting?

you really do scripting-- like with perl right?

you're just an idiot harlan; scripting is built into excel-- it's
called VBA

and the point of the matter is; if you dont have your formulas
DUPLICATED in thousands of different places; then it's not an issue.

DATABASES ALLOW YOU TO DEFINE YOUR FORMULAS ONCE FOR A FIELD INSTEAD OF
ONCE FOR EACH CELL.

i swear to god a few months ago you told me that you didnt have
access... right??
I'm glad that you've finally come to your senses harlan

now you've just got to start using Access Data Projects and maybe i'd
give you a job someday.

the end users that dont have access can use ACCESS RUNTIME. to
add/edit data or run reports.

Office Web Components against Analysis Services gives a VASTLY superior
pivotTable experience to your little baby spreadsheet program.
Anything that is realllllllllllllllly dynamic-- the special numbers--
should be used in a pivotTable.. i mean. .this pivotTable allows you to
display collapsed numbers-- and hierarchies... much like drilldown
SHOULD work in Excel.

I just know that Excel is a disease and I hope that all you lepers come
to your senses soon and start usign a real program.

I mean-- the WORST THING that can happen to you spreadsheet dorks is
the removal of the 65k limit in the next version-- i mean-- that's the
only thing that's been keeping you guys out of trouble so far lol

-Aaron

aaron.kempf@gmail.com wrote...
>scripting?
>
>you really do scripting-- like with perl right?
>
>you're just an idiot harlan; scripting is built into excel-- it's
>called VBA

Well, actually I use gawk for this. I wrote the awk scripts in the late
1990s, before Windows Script Host was available. VBA is a very blunt
tool when it lacks regular expressions and associative arrays. But you
have no idea what these mean, do you?

Also, I really don't like VBA/BASIC. Too verbose and klunky. Why
reinvent the wheel? Oh, that's right. That's what you do.

>and the point of the matter is; if you dont have your formulas
>DUPLICATED in thousands of different places; then it's not an issue.
....

Flexibility! A concept you don't understand.

>DATABASES ALLOW YOU TO DEFINE YOUR FORMULAS ONCE FOR A FIELD INSTEAD OF
>ONCE FOR EACH CELL.

OK, here's another chance for you to show just how much you know about
replacing Excel formulas with ones that would work in SQL code. Linear
interpolation. Given a table named TBL with fields for X and Y values
named X and Y, respectively, also being the first and second fields in
the table. The linear interpolation estimate of a yy value given an xx
value that might not have a match in TBL.X is

=TREND(OFFSET(TBL,MATCH(xx,INDEX(TBL,0,1)),1,2,1),
OFFSET(TBL,MATCH(xx,INDEX(TBL,0,1)),0,2,1),xx)

How would you handle this calculation in pure Access SQL?

>i swear to god a few months ago you told me that you didnt have
>access... right??
....

Wrong again. See http://makeashorterlink.com/?N1EB22BFB

>the end users that dont have access can use ACCESS RUNTIME. to
>add/edit data or run reports.

They just can't create anything. Again, you're foolishly assuming that
most spreadsheet users do what you or your clients do - generate
periodic canned reports. You just can't conceive of anyone using a
computer to do anything that hasn't already been canned, can you?

>Office Web Components against Analysis Services gives a VASTLY superior
>pivotTable experience to your little baby spreadsheet program.

If I used pivot tables, I'd still need to buy Analysis Services (no,
most companies don't use it), and only you could believe programming
OWC is comparable in ease of development to Excel.

>Anything that is realllllllllllllllly dynamic-- the special numbers--
>should be used in a pivotTable.. i mean. .this pivotTable allows you to
>display collapsed numbers-- and hierarchies... much like drilldown
>SHOULD work in Excel.
....

You're assuming all data structures are a mix of hierarchy and n-cubes.
Ain't always so. It may be common in company data already in central
databases, but it's seldom true for customer data.

>I mean-- the WORST THING that can happen to you spreadsheet dorks is
>the removal of the 65k limit in the next version-- i mean-- that's the
>only thing that's been keeping you guys out of trouble so far lol

Finally something we agree on!

Nothing has done so much harm in spreadsheets as the increase from 8K
in old versions of 123 to 16K rows in XL5 and prior then to 64K rows in
XL8 (where K = 2^10 = 1024). The new 2^20 row worksheets will be much,
much worse. As I've written before, anything that requires more than 5K
rows shouldn't be done in a spreadsheet. That's a fraction of the
workbooks that use more than 5K rows, which only goes to show that
there are Excel users who don't know what they're doing. That doesn't
mean they'd do any better using Access.

More columns, OTOH, is needed, but 512 would have been a much safer
number than 2^14.

And I wonder whether Access will have the same quantum increase in
screen clutter/eye wash, er, New & Improved Conditional Formatting
that's due for Excel 12.

hahaha yeah good stuff

yeah.. there are a lot of rules of thumb like that.. i've done some
really really really wide spreadsheets (and tables for that matter)..
like forecasting and budgeting stuff.. for microsoft finance dept..

correct, customers aren't always arranged into neat little hiearchies..
for starters though; i would seperate customers (if i always knew their
first/last name) by the first couple of letters of their name

like for olap dims and all that; you can only have 64k members in a
child (because of the limit in excel they thought that they can get
away with it) but it makes it really hard to manage dims like
'customer'.

it's not programming in OWC; i make a pretty little site where you can
drag and drop-- but it's a hierarchial drilldown type environment..
excel doesnt display hiearchies half as well..

linear interpretation; anything that is recursive like that-- i think
would be easy to do simliar to this code: www.mvps.org/access and
search for BOM-- as in bill of materials. these types of joins make
databases quite friggin powerful and easy to use.

I have done some work with regular expressions and all that.. I think
that they're way too complex for most ****; i mean-- with Access we
have input masks and format strings.. i mean.. input masks take care of
everything i've ever needed out of life; i dont need to deal with
regexp for most stuff..

and to be honest; im strong enough with parsing and all that that i
dont need to regexp anything.. i just loop through stuff-- build my own
expressions. that way i have more control over things.

you know what they really need to do -- if they're increasing columns
this much-- they need to make it easier to import a spreadsheet

A B C D E F G H I J K L M
1
2
3
4
5
6
7
8
9
0

into this shape

1A
1B
1C
1D
1E
1F
1G
1H
2A
2B
2C
3D
4D
5D
6D

just give me the non-empty cells; and make the cells one column and the
formulas another.. maybe i'll need to parse xml to do that; it's
allright i should just be able to use access since it does all the hard
work for me on that anyways lol

i just want to store that in a table like this

XLSID, ROWID, COLUMNID

I'm really looking forward to that one-- i just hope that it's easy to
do.. I've built a half-dozen spreadsheet scrubbing tools over the
years.. basically bring in a whole spreadsheet and then automate taking
sections out of it.. you know what i mean?

im really hoping that the new format does that easier; i mean-- since
it's just xml it's going to be in that format anyways right??

then i can go through and automate 'which cells have changed' and 'how
many formulas changed'-- i've had to do that too many times by hand;
and it would be like impossible to do if more columns get added

do you have any idea what im talking about?

then i scrub through my db and say 'is cell 17 = 36 on these 47
different spreadsheets' or whatever i want to do



-aaron

aaron.kempf@gmail.com wrote...
....
>correct, customers aren't always arranged into neat little hiearchies..
>for starters though; i would seperate customers (if i always knew their
>first/last name) by the first couple of letters of their name

?

We must mean different things by the phrase 'customer data'. I mean
data on a customer's operations and financial condition, not just the
customer's name, primary headquarters address, etc. That is, I focus on
data from a single customer at a time, and that single customer's data
doesn't entirely fit into hierarchies of n-cubes.

>like for olap dims and all that; you can only have 64k members in a
>child (because of the limit in excel they thought that they can get
>away with it) but it makes it really hard to manage dims like
>'customer'.
....

So slot customers by name? I'd have thought it'd make more sense to
slot them dynamically by cumulative historical numbers or total values
of orders or contracts, age from initial transaction, current financial
rating (S&P, Moody, or whatever). Slotting by name would produce an
essentially useless random ordering. If you need multiple dims, might
as well make them meaningful.

>linear interpretation; anything that is recursive like that-- i think
>would be easy to do simliar to this code: www.mvps.org/access and
>search for BOM-- as in bill of materials. these types of joins make
>databases quite friggin powerful and easy to use.

Linear interpolation isn't recursive, unless you interpret any logical
sequence of values to be recursive. In terms of languages that use
arrays, I just mean find a record and the next record. One BIG problem
with SQL-based RDBMSs is that there's no standard way to limit query
results to a set number of records. Each RDBMS has its own syntax. Even
when you do pull just the two records of interest, it appears you need
another query to join the two records so you can use field values from
both records in the same calculation. That's simpler than the
spreadsheet formula I showed?

>I have done some work with regular expressions and all that.. I think
>that they're way too complex for most ****; i mean-- with Access we
>have input masks and format strings.. i mean.. input masks take care of
>everything i've ever needed out of life; i dont need to deal with
>regexp for most stuff..

Input masks are useful but often inadequate when dealing with manual
input, but aren't much use when importing from CSV files that need data
cleansing. Validation ruls, on the other hand, would be improved with
pattern matching more advanced that what the Like operator supports.
Add-on functions could provide access to Windows Script Host regular
expressions, so there's some hope for Access.

>and to be honest; im strong enough with parsing and all that that i
>dont need to regexp anything.. i just loop through stuff-- build my own
>expressions. that way i have more control over things.

You don't understand regular expressions if you believe you have more
control writing your own parsing code. In your sense, you'd have more
control fabricating break shoes for your car rather than buying them
from an auto parts store. Regular expressions fully and completely
describe ANY text more compactly than any code you could write on your
own. There's a reason *ALL* scripting languages now provide them, and
..Net too. What do you suppose that reason might be?

>you know what they really need to do -- if they're increasing columns
>this much-- they need to make it easier to import a spreadsheet
>
> A B C D E F G H I J K L M
>1
>2
>3
>4
>5
>6
>7
>8
>9
>0
>
>into this shape
>
>1A
>1B
>1C
>1D
>1E
>1F
>1G
>1H
>2A
>2B
>2C
>3D
>4D
>5D
>6D

How do you believe cell records are stored in the current BIFF format?
All files are just long linear strings of bytes, so cell contents can't
be stored in any sort of grid. They're stored in sequential records.

>just give me the non-empty cells; and make the cells one column and the
>formulas another.. maybe i'll need to parse xml to do that; it's
>allright i should just be able to use access since it does all the hard
>work for me on that anyways lol

Good luck parsing XML files in Access without regular expressions. Or
state machines.

>then i can go through and automate 'which cells have changed' and 'how
>many formulas changed'-- i've had to do that too many times by hand;
>and it would be like impossible to do if more columns get added

Not so hard. As I mentioned before, I write formula listings to text
files. In the past I've then saved such listings in RCS logfiles, so I
could get RCS diff listings. Not all that helpful when rows or columns
have been inserted or deleted, but it's not all that big a deal to
compare workbooks. Heck, all you need is a udf returning the formula in
a given cell, and you can use cell formulas in one worksheet to compare
cell formulas in any two other worksheets possibly in different
workbooks.

>do you have any idea what im talking about?
....

Having had more experience doing this in spreadsheets than you, yup. I
haven't compared spreadsheets manually since the late 1980s. I may not
fully appreciate your self-inflicted agony.

From
http://www.mvps.org/access/queries/qry0023.htm

As presented in "Joe Celko's SQL for smarties" and discussed in many
newsgroups, the nested set solutions are up to 1000 times faster than
their equivalent methods (mainly based on cursors or recordsets).

While the standard discussions turn around maintenance (adding and
deletion of nodes in the "graph"), there was no example about the BOM
problem based on that kind of solutions, at least, up to now, since
now, you can find one, in the Jet-Access 2000 zipped database included
here. That database has one table, the nested set (we assume you are
familar with the notion), a single form showing graphically the tree
represented in the table, and one query returning the list of the
required elements, and in which quantity, to make an arbitrary item
described in the nested set.

As you can see by yourself, there is no VBA, no recursion, only plain
SQL is used to solve that kind of problem. The query can surely be
re-used for any nested-set, not just for the one given as example.

-----------------------

SELECT
P_2.MemberName,
Exp(Sum(Log(P_1.Qty))) AS RequiredQty
FROM
P,
P AS P_1,
P AS P_2
WHERE
(
((P.MemberID)=[RootNodeID])
AND
((P_1.lft)
Between [P].[lft]+1
And
[P].[rgt])
AND
((P_2.lft)=[P_2].[rgt]-1
And
(P_2.lft)
Between [P_1].[lft]
And
[P_1].[rgt]
))
GROUP BY P_2.MemberName;


yeah.. so uh-- now that you can write recursive queries in plain old
sql are you going to stop bitching about all this 'oh, its sooo
complex.. it's recursive'

**** kid lose the training wheels

procedural programming.. gosh

i never said i was anti-procedural programming.. im more of a
procedural
guy than most people-- most people do more object oriented stuff; i
just
build small light apps so i dont have to worry about building
components
most of the time.. and the only components i ever use are from
microsoft.. things like ADO and Office Web Components-- light fun
stuff.


and the rest of the time; i just prefer using plain old html; dont need
much in the way of objects to spit out some pretty asp reports

between the two; i feel quite powerful.

it's frustrating to me that we have people that are falling in love
with
sharepoint-- when it's the wrong architecture-- just because it's a
simple easy to use frontend to a database.

It's like... People are going from Texas to Atlanta.. when all the jobs
are in really in California. So it's like-- I just feel bad

for NVP, i'll just use a prewritten UDF

http://www.sqlteam.com/Forums/post.a...ID=23307&FORUM
_ID=11

SELECT @npv = SUM(cf*power(1+@rate,-pid))
FROM test
return(@npv)



here is the full text:
-------------------------------------------------
create table test (pid int not null, cf money not null)
go
set nocount on
insert test (pid,cf) values (0,-100)
insert test (pid,cf) values (1,10)
insert test (pid,cf) values (2,10)
insert test (pid,cf) values (3,10)
insert test (pid,cf) values (4,10)
insert test (pid,cf) values (5,10)
insert test (pid,cf) values (6,10)
insert test (pid,cf) values (7,10)
insert test (pid,cf) values (8,10)
insert test (pid,cf) values (9,10)
insert test (pid,cf) values (10,10)
insert test (pid,cf) values (11,10)
insert test (pid,cf) values (12,10)
insert test (pid,cf) values (13,10)
insert test (pid,cf) values (14,10)
insert test (pid,cf) values (15,10)
insert test (pid,cf) values (16,10)
insert test (pid,cf) values (17,10)
insert test (pid,cf) values (18,10)
insert test (pid,cf) values (19,10)
insert test (pid,cf) values (20,10)
set nocount off
go
create function dbo.npv (@rate real) returns real
begin
declare @npv real -- return value

SELECT @npv = SUM(cf*power(1+@rate,-pid))
FROM test
return(@npv)


of course, i'd make it a little more flexible.. in order to be able to
pull from any table.

cheers


-Aaron

ps - looks to me like irr is a lot more complex.. im pretty sure I
could
force some cartesian in order to generate enough test data in order to
make lookups on this a lot faster. I just know that I could house some
huge cartesian based on days and investments and it would be pretty cut
and dry to cut through it with olap.

i mean.. it's fast as snot

aaron.kempf@gmail.com wrote...
....
>i never said i was anti-procedural programming.. . . .
....

Then you fail to understand that most spreadsheet users aren't
programmers. For nonprogrammers, the essentially functional programming
nature of spreadsheet formulas is easier to learn and use than
procedural code. That's one reason why so many people use spreadsheets.

>for NVP, i'll just use a prewritten UDF
>
>http://www.sqlteam.com/Forums/post.a...ID=23307&FORUM
>_ID=11
>
>SELECT @npv = SUM(cf*power(1+@rate,-pid))
>FROM test
>return(@npv)

I deserved that. Your simplified approach may work when the period
field (pid) contains integers starting at 0. It won't when the period
field contains dates or when there's no period field but the cashflows
are ordered properly anyway.

>here is the full text:
>-------------------------------------------------
>create table test (pid int not null, cf money not null)
>go
>set nocount on
>insert test (pid,cf) values (0,-100)
>insert test (pid,cf) values (1,10)
>insert test (pid,cf) values (2,10)
>insert test (pid,cf) values (3,10)
>insert test (pid,cf) values (4,10)
>insert test (pid,cf) values (5,10)
>insert test (pid,cf) values (6,10)
>insert test (pid,cf) values (7,10)
>insert test (pid,cf) values (8,10)
>insert test (pid,cf) values (9,10)
>insert test (pid,cf) values (10,10)
>insert test (pid,cf) values (11,10)
>insert test (pid,cf) values (12,10)
>insert test (pid,cf) values (13,10)
>insert test (pid,cf) values (14,10)
>insert test (pid,cf) values (15,10)
>insert test (pid,cf) values (16,10)
>insert test (pid,cf) values (17,10)
>insert test (pid,cf) values (18,10)
>insert test (pid,cf) values (19,10)
>insert test (pid,cf) values (20,10)
>set nocount off
>go
>create function dbo.npv (@rate real) returns real
>begin
>declare @npv real -- return value
>
>SELECT @npv = SUM(cf*power(1+@rate,-pid))
>FROM test
>return(@npv)

The only problem with this is that it depends on the table test which
you created at the top of the code. How do you generalize it to take
cashflows from fields of any name from any given table?

>of course, i'd make it a little more flexible.. in order to be able to
>pull from any table.

So what's stopping you?

>ps - looks to me like irr is a lot more complex.. im pretty sure I
>could force some cartesian in order to generate enough test data in order
>to make lookups on this a lot faster. I just know that I could house
>some huge cartesian based on days and investments and it would be pretty
>cut and dry to cut through it with olap.

In the IRR calculation the cashflows are static and NPvs are repeatedly
calculated at different interest rates. Only the interest rate varies,
and deciding what to use for the next interest rate follows some
variant of the Newton-Raphson approach to finding zeros of functions.
It looks like the code at the url you gave uses the secant method. Your
cartesian would be a colossal waste of processing time and system
resources since the algorithm used is mathematically optimal barring
pathological data.

aaron.kempf@gmail.com wrote...
>From
>http://www.mvps.org/access/queries/qry0023.htm
>
>As presented in "Joe Celko's SQL for smarties" and discussed in many
>newsgroups, the nested set solutions are up to 1000 times faster than
>their equivalent methods (mainly based on cursors or recordsets).
....

How is this remotely relevant to linear interpolation? [And how is this
continued back & forth remotely relevent to this thread or perhaps even
this newsgroup? But I digress.]

Nice link. Note the error in the Summary form that supposedly explains
the structure: the second G appearing as a child of F. It should be I
rather than G.

Hierarchical structures are useful, and they're fast. Trees and doubly
linked lists have been used in most programming languages for decades.
That they can be implemented in SQL as well is no big deal.

However, they're at best beside the point when it comes to simple array
indexing, which is pretty much all that's required to fetch the values
from a table that are needed for any type of interpolation. Like using
a freight train to go grocery shopping.

>yeah.. so uh-- now that you can write recursive queries in plain old
>sql are you going to stop bitching about all this 'oh, its sooo
>complex.. it's recursive'

All you have to do is create the nested set to begin with. And how
simple is that? And would average users immediately understand how to
set them up? And do you really believe debugging errors in the nested
set table would be easier than debugging spreadsheet formulas?! What
you smokin'?!!

Recursive queries (walking trees) aren't the same as recursive function
evaluation. And they're nowhere close to simple array indexing.

aaron.kempf@gmail.com wrote...
>most spreadsheet developers ARE programmers.
....

I agree that spreadsheet *developers* (people who write spreadsheet
models used by other people) are programmers, but a substantial
fraction of them wouldn't call themselves programmers, and fewer still
have any formal training as programmers.

>I dont think that Excel does procedural programmming better than
>Access. other than the bs that you can record macros in Excel but you
>can't record macros in Access.

Since both use VBA, they wouldn't differ, would they? But there's
seldom the need to resort to VBA for calculations in Excel. As long as
one's willing to use an arbitrary number of cells, almost any
calculation can be done using worksheet formulas exclusively.

>and OLAP-- Analysis Services-- it provides is the most powerful
>PivotTables. OLAP pivotTables-- from Analysis Services-- are superior
>to normal pivotTables by a factor of about 1000. OWC with OLAP is
>about 1,000,000 times more powerful than normal silly excel
>pivotTables.

OK. I don't use pivot tables. Never found them useful. Next point?

>Since MDX is FREE with SQL Server and SQL Server is FREE with Windows--
>then you're pretty much an idiot for not using it.

I suppose you mean SQL Server in the form of MSDE is free. Other
editions of SQL Server aren't free.

http://www.microsoft.com/sql/howtobuy/default.mspx

MSDE comes with MDX?

because I can interpolate with real data using olap

i can cartesian a couple of tables--

i just think that it's funny

the only thing that you're talking about doing is trying data for lots
and lots of time slices

you store those time slices in a TABLE and then you can cartesian date
and flow where you want and you'll get whatever kind of ratio you're
looking for.

and it wont be iterative. you wont write a loop. it'll be a JOIN.

yes, i believe that

a) let db developers write the functions
b) you guys can use these functions in a sql database-- MSDE is you
want

the only single reason that you think that databases 'arent as
powerful' is because they dont have all the cheesy little functions
you're looking for.

I will try to compile some list of functions for making SQL behave more
like Excel.

Ok?

someday I'll try to write that.. just a couple of googles; and you'll
be able to use any of these cheesy Excel functions you want-- but
you'll do it in a database

i mean-- if that is what it takes to wean you off of excel; i will do
it throw together a list someday and share it with all you folk

aaron.kempf@gmail.com wrote...
>because I can interpolate with real data using olap
>
>i can cartesian a couple of tables--

Linear interpolation involves *one* table. What's this 'couple of
tables'? Do you understand the concept? Tables showing the standard
normal distributions may be found in most statistics texts. One of the
more common tasks is figuring out critical values for significance
testing. What's the critical value for the two-tailed 20% significance
level? The relevant portion of the standard normal distribution table
would be

1.28 .89973
1.29 .90147

Two-tailed 10% significance level means z = .95. The critical value to
3 decimal places is then 1.282 =
(1.28*(0.90147-0.9)+1.29*(0.9-0.89973))/(0.90147-0.89973).

Could you use a cartesian product of the table with itself? I suppose
so. Seems wasteful, but I suppose database developers like you have yet
to figure out how to fetch numbers taking up total storage of 32 bytes
without creating multiple megabyte temporary data structures.

>i just think that it's funny

Whereas I'd call your approach sad. Perhaps pitiful would be closer.

>the only thing that you're talking about doing is trying data for lots
>and lots of time slices

Wrong in the particulars.

>you store those time slices in a TABLE and then you can cartesian date
>and flow where you want and you'll get whatever kind of ratio you're
>looking for.

Cartesian is still gross overkill compared to simple array indexing,
but I'll take you word for it that it may be the best databases can
manage.

>and it wont be iterative. you wont write a loop. it'll be a JOIN.

Go back a few of my posts. You'll see two simple SELECT queries for the
two records needed. Simple enough to join them to put everything into a
single record, then use an expression based on those fields to produce
the linear interpolation result.

Other than the obvious (you have no clue how to do this), why would you
bother with a cartesian?

>the only single reason that you think that databases 'arent as
>powerful' is because they dont have all the cheesy little functions
>you're looking for.

Excel doesn't have a linear interpolation function either, but it's
MUCH EASIER to write an expression to return a linear interpolation
result in Excel than it is in Access or SQL Server with all the add-on
software you seem to need to use.

no that's not true

if i knew what the formula was; it would be easier on the database
side-- because i'd write this function ONCE and use it anywhere--
instead of needign to copy this function to 2,000 different
spreadsheets

i just know that there isn't a damn thing that i can caclulate in plain
old sql server. sql server-- MSDE-- is free with Windows effectively.

i just think that it's a shame that you guys haven't grown up. I
mean-- it is 2005. It's not really like Excel is MORE POWERFUL than
databases.

it's just sad that you work for a company that doesn't provide
databases for you.

i mean-- databases should be AVAILABLE
instead of each person having their own H drive; each person should
have their own SQL Server database-- start keeping this data in a place
where you can USE it instead of having to always be copying and pasting
data around

write simple reports off of this data

i mean-- you guys sit there and say 'oh but i dont make reports'-- yes
you do make reports.

you sit there and copy and paste your life away.

and there is a better way.

there are more efficient ways to do math than to have 20k copies of the
same formula with different arguments


-Aaron

aaron.kempf@gmail.com wrote...
>no that's not true

=BFQue?

>if i knew what the formula was; it would be easier on the database
>side-- because i'd write this function ONCE and use it anywhere--
>instead of needign to copy this function to 2,000 different
>spreadsheets

On the spreadsheet side it's simple enough to write an interpolation
formula once in VBA.


Function li(xx As Double, x As Variant, y As Variant) As Variant
'minimal error checking
Dim n As Long
On Error Resume Next
n =3D Application.WorksheetFunction.Match(xx, x)
If Err.Number <> 0 Then
li =3D CVErr(xlErrValue)
Else
li =3D (y(n) * (x(n + 1) - xx) + y(n + 1) * (xx - x(n))) _
/ (x(n + 1) - x(n))
If Err.Number <> 0 Then li =3D CVErr(xlErrRef)
End If
Err.Clear
End Function


Then I could call it as needed. Only problem is that udfs in Excel are
slow, so I'd stick to the TREND formula approach.

=3DTREND(OFFSET(y,MATCH(xx,x),0,2,1),OFFSET(x,MATCH(xx,x),0,2,1),xx)

>i just know that there isn't a damn thing that i can caclulate in plain
>old sql server. sql server-- MSDE-- is free with Windows effectively.

I agree. There isn't a damn thing you *CAN* calculate in plain old SQL
Server. [Proof reading's a *****, ain't it?]

>it's just sad that you work for a company that doesn't provide
>databases for you.

As I've said before, I have Access on my own PC and Oracle on the
servers. They're not real RDBMSs? Only SQL Server suffices for you?

>i mean-- you guys sit there and say 'oh but i dont make reports'-- yes
>you do make reports.

Define 'report'.

I generate exhibits other people use. Most of them are based on tested
templates that haven't needed major revisions in years. And nearly all
the data in them comes from e-mail. Some cut & paste? From time to
time. More often it's just detaching files and parsing them. Generally
less than a half hour from e-mail receipt to completion of exhibit.
It'd take longer to bash the data into a database (it's from different
customers, so the formats are never the same).

Putting it differently, I don't use much in-house data, and when I do I
tend to use Access (believe it or not). I definitely don't produce
canned reports which you seem to believe are the pinacle of business
workproduct.

>there are more efficient ways to do math than to have 20k copies of the
>same formula with different arguments

If all these formulas were in memory at the same time, you'd have a
point. If spreadsheets like Excel didn't provide minimal recalculation,
you'd have a point. However, only the formulas in the open workbooks
are in memory using the CPU, and only the formulas that need to be
recalced are recalced, and whether it's a spreadsheet with 1,000
identical formulas or a database calculating the same expression for
1,000 records, 1,000 calculations in one isn't much different than
1,000 calculations in the other.

If you mean storage redundancy, then you have a fair point. However,
it's a trade-off between storage usage and flexibility. You just don't
grasp that concept.

i just dont see a single benefit to excel

and i never will

it is a disease

Aaron,

a worse disease is YOU: you spread stupidity and ignorance.

Get a life and get a brain.