Re: why>?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

dbahooker@xxxxxxxxxxx wrote...
yeah of course i've heard of the round function.

i dont want to create another 50,000 formulas that are impossible to
validate.

Formulas like

=ROUND(B5,2)

are impossible to validate? Holding impossibility as axiomatic does
mean there's no need to assess the claim. Just doesn't conform well to
reality or rationalism. But you're not here to be rational, are you?

I want to use Excel as an ETL tool--much like you dipshits do every
day.

If data is already in XLS files, maybe I'd use Excel for this, but when
I get data in text files or PDF files (which I convert to plain text
using console-mode utilities) I use awk or Perl myself. Apparently you
don't know how to use scripting languages either.

I dont want 'another copy' of my data.

So don't save the copy. However, don't confuse loading data into
volatile memory with redundant copies. And also realize the caching,
ergo redundancy, makes OLAP as fast as it is compared to old-style SQL
RDBMS views.

i want simplification.

Well you've made your arguments about as simplistic as possible, so
you're off to a fine start.

Im saying there should be something; similiar to formatting-- where you
can edit the raw data.
AND IT SHOULD BE EASY TO DO.

So how do you do this in databases? If a field contains doubles,
rounding those values involves an update query. The closest Excel
equivalent is a macro like

Sub foo()
Static pf As String

Dim cf As String, tf As String
Dim c As Range, v As Variant

If Not TypeOf Selection Is Range Then Exit Sub

tf = InputBox( _
Prompt:="Enter formula template" & vbCrLf & _
"@ for current cell ref" & vbCrLf & _
"@@ for literal @", _
Title:="Batch Transform", _
Default:=pf _
)

If tf = "" Then Exit Sub Else pf = tf

For Each c In Selection
cf = Replace(tf, "@@", Chr(127))
cf = Replace(cf, "@", c.Address(1, 1))
cf = Replace(cf, Chr(127), "@")
v = Evaluate(cf)
If Not IsError(v) Then
c.Formula = v
ElseIf c.Comment Is Nothing Then
c.AddComment cf & " failed"
Else
c.Comment.Text c.Comment.Text & vbLf & cf & " failed"
End If
Next c
End Sub

so instead of having 30 numbers with
345.4534534545989898493584359458989 you can update that to only store
345.453

You really don't understand how numeric types are represented in memory
or stored on disk, do you? Whether a particular field holds 0.25 or
0.290802001953125 [=SUMPRODUCT(2^-{2;5;7;10;11;12;15})], if it's stored
as type Double it *ALWAYS* uses 64 bits and *ONLY* 64 bits. All numeric
types provided by Access and SQL Server (acording to Microsoft's web
site) use *FIXED* numbers of bits for numeric storage, with the number
of bits varying by numeric data type. Only databases that store numbers
as text or variable numbers of bytes (like some mainframe dbms's that
provide packed decimals) would realize reduction in storage by reducing
numeric precision.

What an expert you are!?

it's not that difficult.

True. The macro above is pretty clear. And the macro shows that it's
already possible to do this pretty efficiently.

in the database world; it is easy to run a simple update statement to
update a field.

Granted. While this functionality isn't built into Excel, it's pretty
simple to add it, that is, if you know what you're doing.

I wasn't looking for a select statement that would change the raw data.
in a database i can use an update statement.

Different syntax, similar underlying procedure.

in Excel; there isn't a seperation betwen updating and selecting.
there should be.

Ambiguous. Maybe you mean 'select' in the SQL sense, but in case you
mean it in the Excel sense, look at the macro above. It operates over
the selected range, but it doesn't change the active cell. It could
easily be changed to prompt the user to specify the range to be
processed, and the user could do that either by selecting the range or
entering the range address. The remainder of the macro wouldn't need to
select anything.

i should be able to right-click on a *** and say 'get rid of all
decimal places'

Why should Excel provide this?

But if you want to eliminate all fractional parts of all numbers
(assuming no number is is so large or small it's represented in
scientific notation),

1. select the entire work*** (press [Ctrl]+A),
2. press [F5] (GoTo),
3. click on Special... or press [Alt]+S,
4. select Constants of type Number only,
5. click OK or press [Enter],
6. run Edit > Replace, replacing .* with nothing.

If this is still much too much for you, record it as a macro.

do you know why?

BECAUSE IM SICK AND TIRED OF HAVING TO DEAL WITH BULL*** PEOPLE
THAT CANT FOLLOW RULES.
....

Which is why you need to return nothing but error values and diagnostic
messages if users enter ANY invalid data, and contact their managers if
they then try to print. This is a VERY EFFECTIVE approach to dealing
with lazy/careless/unmotivated users. As long as you let them save and
correct at a later time, they quickly adapt to not printing until all
entries are valid.

If excel had INPUT MASKS maybe it wouldn't be such a PITA
....

We agree on this. In general, if Microsoft would address the
deficiencies of data validation, Excel would be a much better product.
However, cynical me believes MSFT would think this would reduce
Access/Office Pro sales, so it ain't gonna happen.

.

Quantcast