Re: Computing Averages in MS Excel

"dawn" <dawnwolthuis_at_gmail.com> wrote in message news:1134059689.752533.87750_at_f14g2000cwb.googlegroups.com...

> If I'm understanding, that is the way that Pick handles it. That is
> why end-users never give NULL a thought there. It averages only what
> is there. The problem arises if you add SQL to the mix and say that
> you have a numeric attribute. Notice that Excel doesn't have strong
> typing. You can likely execute a SQL query against Excel using some
> tool or other. I wonder how it handles the column if you request an
> average of all numbers from SQL and there is data missing in some
> cells. I'm guessing it would give an error. That is the problem I had
> with a SQL wrapper on a 2VL system. If a column was defined as numeric
> in any way, it seemed to want numbers in it. Funny thing, eh?
>

As you and I have agreed elsewhere, a better wrapper would have admitted two kinds of things in a numeric column:Either a number, or nothing.

And it would have recognized "nothing" in whatever form the underlying data system uses to express that. And in interpreting "there is no value here" into SQLish, it would have used NULL, since that is the way you say that in SQLish.

BTW, my rephrase of the problem into how to express nothing in Excel turns the original question of "SQL wrapper" on its head. Suppose you have an SQL Wrapper around a bunch of well defined, current, and accurate Oracle tables. Suppose that, on top of this you want to present this stuff in some presentation environment like MS Excel.

In other words, what if you have SQL, and you want to wrap than in Excel? How to you represent NULLS?
My answer is simple: use the Excel empty cell whereever you see a null. And I think that's how the interface between MS Excel and MS Access does it.

When I need to move data from one environment to another, I generally use "wizards", instead of programming. I'll program, but only as a last resort. In Microsoft office, most wizards start off with a killer of a question: "Where is your input?"

After you get over that hurdle the rest is easy.

So the art of planning things so that the wizards can do their stuff, and I can get what I want turns into a project of strategic data design. I don't want to call this kind of design "conceptual", "logical" or "physical", because it's a practical melange of all three. But it has to be built in such a way that, when the Wizard takes an initial look at your data, it's stuff that the wizard knows how to read, and how to interpret correctly.

That means that, for each wizard, you have to understand that wizard's inputs and that wizard's outputs. But you don't have to understand how the wizard works its wizardry. It's not pretty bu it beats programming 90% of the time. Received on Fri Dec 09 2005 - 14:57:37 CET