OT: Spreadsheet formula question (openoffice)

Sep 17, 2009 3 Replies
W

I'm coming up blank tonight. I have a table of values that I want to calculate various statistics on ranges of values.



I'm sure there is a way that I can just have a cell with row start and second cell with row ending and format that so that various functions are given the proper start and end range to calculate without me having to modify every darn function?


Thanks,



Wes


Vote
Twitter Facebook LinkedIn
J

The offset function OFFSET(reference; rows; columns; height; width) and some other forms as shown at

might do what you want. Also see the example at

formatting link
which works ok with the following exceptions: (1) Use oocalc's semicolon rather than excel's comma between OFFSET arguments; (2) In the 'Insert, Name, Create; choose Left Column' step, oocalc stored names _Rows and _Cols rather than Rows and Cols; (3) F5 didn't invoke the GoTo dialog referred to in the example and I don't know how to invoke it.

Anyhow, I put =sum(MyData) into a cell, and verified that the number displayed in the cell changed appropriately when I changed values of numbers in B1...B4, indicating that MyData was a dynamically calculated range.

The simpler example at

will be relevant if all you want is a name assigned to a range of cells, rather than the dynamic range supported by OFFSET.

Vote
Twitter Facebook LinkedIn
L

James Waldby fired this volley in news:h5adnfsHF- GAUS_XnZ2dnUVZ snipped-for-privacy@bresnan.com:

ACtually, he might be wanting the INDIRECT function, instead.

He said he wanted a cell (or cell pair) that would describe the beginning and ending data. INDIRECT seems well-suited to that.

LLoyd

Vote
Twitter Facebook LinkedIn
W

Indirect worked. Btw, it was used in the link that James kindly provided.

Thanks to both of you.

Wes

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required