LibreOffice Calc help needed

[DoYouKubuntu]

Spreadsheets are still the best way to do the actual data analysis (aka business "intelligence" work.) Most data warehousing projects pivot tables -- make the database columns spreadsheet rows. Microsoft Excel even connects to SQL Server's analysis services directly... they call it PowerPivot or something unimaginative like that.

That's very interesting, and it's news to me. As I've said, spreadsheets have never been my thing, so I'm really pretty clueless as to what they can do!

[DoYouKubuntu]

I make spreadsheets for use in the field by people who are clueless about the simplest things - like when I want you to enter a number don't put in a letter. So I spend a LOT of time coming up with creative ways to prevent mis-entries and lots of guidance.

Those are things I did--easily--in databases [using filePro] and Bourne shell scripts. And because of that history, knowing how simply and elegantly things can work the way I want, I assumed spreadsheets offered the same. I was apparently wrong! Oh well, it's a good thing I don't have to learn how to REALLY do that stuff in spreadsheets at this advanced age, because I think it would be a lost cause.

The real power in Excel lies in the VBA code. I do stuff like make pivot tables auto-refresh and whole tables of data auto-sort (because a HUGE button that;s labeled "SORT" on it is too friggin' hard for some of these dummies to figure out ). I even wrote batch of code the makes a new sheet that contains a template for data entry from a list of titles and types - it even enters the sheet title and auto-colors the tab based on what type of data it is. It's a lot of work at the beginning, but better for distribution.

Sounds interesting. And, yes, I totally get the "too friggin' hard" thing. * nod *

[oshunluvr]

I'd actually like to see some like you're describing, just to see how they work!

I make spreadsheets for use in the field by people who are clueless about the simplest things - like when I want you to enter a number don't put in a letter. So I spend a LOT of time coming up with creative ways to prevent mis-entries and lots of guidance.
The real power in Excel lies in the VBA code. I do stuff like make pivot tables auto-refresh and whole tables of data auto-sort (because a HUGE button that;s labeled "SORT" on it is too friggin' hard for some of these dummies to figure out ). I even wrote batch of code the makes a new sheet that contains a template for data entry from a list of titles and types - it even enters the sheet title and auto-colors the tab based on what type of data it is. It's a lot of work at the beginning, but better for distribution.

[InsideJob]

Spreadsheets are still the best way to do the actual data analysis (aka business "intelligence" work.) Most data warehousing projects pivot tables -- make the database columns spreadsheet rows. Microsoft Excel even connects to SQL Server's analysis services directly... they call it PowerPivot or something unimaginative like that.

[DoYouKubuntu]

I suspect you're right. That's what I was alluding to, anyway.

Yeah, I kind of figured that.

Although I do have a couple 90+ sheet Excel workbooks replete with 1000's of lookups, data validations, and 8 or 9 pages of VBA code. They tend to eat a bit of power and RAM...

Spreadsheets have never been my thing--databases were/are. I know spreadsheets CAN do a lot, but they've just never been a necessity for me. I'd actually like to see some like you're describing, just to see how they work!

[oshunluvr]

I don't actually know the answer to this, but I feel pretty confident that this is a good guess: It's a non-issue with today's hardware.

I suspect you're right. That's what I was alluding to, anyway. Although I do have a couple 90+ sheet Excel workbooks replete with 1000's of lookups, data validations, and 8 or 9 pages of VBA code. They tend to eat a bit of power and RAM...

[DoYouKubuntu]

Curious kubicle, I always used my example because I assumed direct adding (simple) required less processing than a function.

I wonder with 100,000 iterations if either method would prove superior time-wise?

I don't actually know the answer to this, but I feel pretty confident that this is a good guess: It's a non-issue with today's hardware.

Likely, my opinion is a hold-over from my Apple IIc and Appleworks days, LOL - 1 MHZ 8-bit processor and 128KB of RAM

Man, when I upgraded it to a 16MHZ CPU, did my spreadsheets ever FLY!

[oshunluvr]

Curious kubicle, I always used my example because I assumed direct adding (simple) required less processing than a function.

I wonder with 100,000 iterations if either method would prove superior time-wise?

Likely, my opinion is a hold-over from my Apple IIc and Appleworks days, LOL - 1 MHZ 8-bit processor and 128KB of RAM

Man, when I upgraded it to a 16MHZ CPU, did my spreadsheets ever FLY!

[DoYouKubuntu]

Okay, guys, thanks for the help and examples. I guess I'm just too much of a database programmer at heart--I expected to be able to define something, once, store it as a 'program' or something, and then just have it work as wanted. In other words, an elegant solution.

A good formula for a "running total of column A into column B" is:
=SUM(A$1:A1)

But I'm not bitching! The method above works and I'm now a happy camper.

[kubicle]

A good formula for a "running total of column A into column B" is:
=SUM(A$1:A1)
assuming the values start in cell A1 and you put the formula in B1, the '$' in '$1' means that number won't change as you drag copy the formula down in the B column. So the effective formula in cell B100, for example, will be =SUM(A$1:A100) after copying down.

[Qqmike]

As I recall, there's some fast ways to do this copying (copy a formula down a column).
https://www.google.com/search?client...utf-8&oe=utf-8
For Excel: 3 ways, http://www.pryor.com/blog/copy-excel...fill-a-column/
The dragging method is nice. Haven't used a spreadsheet for many years. Well, I have: yellow tablet paper, a pencil, and an HP calculator.

Edit: Looks like Snowhog mentions the dragging method, I see now.

[Snowhog]

You put a 'formula' in the first cell in column B: =A1
You put a second 'formula' in the second cell in column B: +B1+A2
Copy the 'formula' that is in cell B2 and click in cell B3 and 'highlight' (click+drag) down the B column for as many cells as you need and paste the copied formula.

[DoYouKubuntu]

Column A = Numbers

Column B

B1 = A1
B2 = B1+A2
B3 = B2+A3
...

Where do I put this? And surely you don't mean that I have to keep entering all the values, like B213, right?

[oshunluvr]

Column A = Numbers

Column B

B1 = A1
B2 = B1+A2
B3 = B2+A3
...

[DoYouKubuntu]

Can someone give me a crash course on how to do what MUST be a simple thing in Calc?

. assume there are two columns, A and B

. assume that A contains user-entered numeric values

. when leaving field A, I want B to keep a running total, like this:

A               B
100             100
150             250
 25             275
200             475

How?!