OpenOffice spreadsheet question

Factual Questions
1

Does anyone know how to make pivot tables in OpenOffice 1.0? I know it can do it, as I’ve opened Excel files in OO with pivot tables and they work. But I can’t figure out how to make them. Someone said “Data Pilot” has something to do with it, but I can’t figure out WTF that thing is supposed to do. It doesn’t seem to want to make me a pivot table, anyway.

Thanks.

2

This is admittedly just a WAG, since I’ve played with OpenOffice only a couple of times (enough to know that its version of Excel was lacking some things I need, like array formulas), so take it with a huge grain of salt.

I believe that when you create a pivot table, Excel is just automatically creating the otherwise difficult coding required to produce the resulting table. In other words, once you’ve created the table, there aren’t some kind of special “pivot table” codes embedded in the spreadsheet, but rather just a bunch of standard Excel coding that would be difficult and time-consuming to create on your own.

So, when you open that spreadsheet in OpenOffice, the table works fine, but OpenOffice on its own doesn’t have the capability to generate the coding necessary to produce a new pivot table.

(Since I’m just guessing, I’ll happily retract all of this if someone comes along who knows better!)

3

Um, the correct name of the program is OpenOffice.org. Please don’t call it “OpenOffice”, as this has trademark issues. See this explanation from the developers.

UnuMondo

4

Early Out: Seems you’re wrong. I tried making a pivot table in Excel, and then tried the Data Pilot in Openoffice.org. I don’t understand what the problem is, friedo. The Data Pilot is almost exactly the same as the Excel Pivot Table Wizard…if you can use one, the other…well, it’s virtually the same thing.

5

Early Out: Forgot to ask: Are array formulas the same as what OOo Calc calls matrix functions?

6

Hehehe. Well I’ve never actually used the Excel one, you see. I’ve just opened files made by other people with OpenOffice.org. So obviously I’m missing something about how this Data Pilot thingy is supposed to work. Let’s say I’ve got the following data:



        A                B                C                  D
    ________________________________
1 |                    Jan             Feb              Mar
2 | Alice
3 | Bob
4 | Carla

Now I want to add a pivot table for row 2, which adds a third criterion, (a state code) so one could then see a list of total sales made by Alice in January for each US state. Does that make sense? If so, for the life of me, I can’t figure out how to make Data Pilot work.

(And of course I want a similar deal for Bob and Carla too.)

7

Ahh…I see. Sorry, can’t really help because I always hated Pivot tables in Excel because I got used to the way they worked in SPSS.

I would ask one of your friends who send you them how to make one, or make a new GQ asking how they’re made in Excel (which more people are likely to answer). As I say, it’s exactly the same in OOo.

I suppose it’s one of these things I ought to know though, so I’ll find out and send you a reply if you’ve had no luck in that time…

8

Ah, dylan_73, it appears that our friend friedo’s problem isn’t with the software, but with the user! In that case, my WAG is clearly worthless, since OOo is capable of doing pivot tables. Oh, well.

It’s tough to describe what array formulas do in Excel, and I don’t have a copy of OOo to play with any longer (and disliked it enough that I wouldn’t reinstall it on my new machine), but here’s an example. Say you’ve got a two-column spreadsheet. Column A contains a bunch of values. Column B contains some blanks, and some cells with “x” in them (as in, "has the check cleared or not?).

Now, you want a single cell somewhere that is the total of the values in Col A, but only for those rows that also have an “x” in Col B. Without array formulas, you’d have to create a third column (blank if Col B is blank, copy the value from Col A if Col B = “x”), then add up the resulting values in Col C. Array formulas let you do it in one step. The resulting formula would look like this:

{=SUM(IF(B1:B1000=“x”,A1:A1000))}

though you can’t enter it directly by typing in the curly braces (for reasons that escape me); you enter it without the curly braces, but then hit CTRL-SHIFT-ENTER.

There are a lot of more complex uses for the beast, of course, but that excercise is left to the student.

9

Now, to take a stab at friedo’s problem. This is a bit like trying to thread a needle with mittens on, however. This is one of those problems that needs a hands-on demonstration.

The pivot table doesn’t let you create new criteria. It just lets you organize your existing data in different ways. You wouldn’t use a pivot table to add information about sales by state. You’d use a pivot table to take your existing data and put it into different totaling buckets.

Let’s say you had sales for each person. Col A might be the person’s name, Col B the amount of the sale, Col C the month the sale was made, Col D the state in which the sale was made. There’s one row for each sale:

Jane $5 Feb CA
Jane $4 Jan AZ
Mary $2 Apr WY
Mary $8 Sep MT

and so on.

A pivot table would let you display a table of results with totals by salesperson, by month, or by state. You could easily have the states running down the left side, and the months running across the tops of the columns, with the total sales by state, by month, in the field of the table. Or, you could have the salepeople running down the left side, and the states running across, with the total sales by salesperson, by state, in the field of the table.