On 11-May-13 2:05 PM, Fred James wrote:
> Denis Heidtmann wrote:
>> INDEX(A1:Z1,1,COUNTA(A1:Z1),1)
>>
>> where it is assumed that the Z1 is as far in the row as your data could be.
>> Otherwise change Z1 to whatever you want. Also, this assumes that there
>> will be no blanks in the columns to the left of the last.
>>
>> -Denis
>>
>>
>> On Sat, May 11, 2013 at 7:42 AM, David Fleck<david.fl...@mchsi.com> wrote:
>>
>>> I don't use spreadsheets very often, so my cell-formula-fu is quite
>>> limited. I'm trying to come up with a way of having a cell (let's call it
>>> 'A1') contain the value of another cell. That other cell will be the last
>>> occupied column of row 1, and it will vary over time. So sometimes I want
>>> A1 to contain the value of E1, because that's the last occupied column;
>>> but other times it should contain the value of W1, because that is the
>>> last occupied column. I rummaged through the Help, but didn't see
>>> anything that obviously solved my problem.
>>>
>>> Can this be done?
>>>
>>> If so, can someone explain how?
>>>
>>> Thanks in advance.
>>>
>>> --
>>> David Fleck
>>> david.fl...@mchsi.com
>>>
> Denis Heidtmann
> I am trying to impliment your interesting solution, but I keep getting ...
> ERR:508; Error:Missing pair,;"Missing bracket, for example, closing
> brackets, but no opening brackets "
> Thank you for any further assistance you may be able to offer
> Regards
> Fred James
Arrg, @#%! I messed up.
The syntax for index() should be INDEX( range, row, column, area ) In
my original response I had row and column transposed. Also, I'm using
excel here because that's what I have installed on a 17 yr old Dell
Inspiron 5000. Anyway in Calc the parameter delimiter is a semi-colon,
but it's a comma in Excel. SOooo... the two functions work in tandem;
COUNTA() counts the number of items in the range, and it uses that value
to tell INDEX what row number is last. If you read the documentation
(and you should) you will see COUNTA() skips blank cells that might be
in the range/list. You'll save yourself a lot of effort if you adopt a
non-null "no data" indicator instead of leaving empty cells in your
data. I'd recommend using one single-quote in cells with no data.
Here's an example of these two functions in action. Suppose we have a
list of colors in C3:C9 like so:
|___A ___|___B___|___C________|
1 | indigo 7
2|
3| infra-red
4 |red
5|orange
6|yellow
7|green
8|blue
9|indigo
Cell B1 contains the function COUNTA(), and the parameter is the range
of colors, "C3:C9". Cell A1 contains the INDEX function which calls
COUNTA function, and looks like this:
INDEX(C3:C9, COUNTA(C3:C9), 1, 1 )
This returns the value of the cell in the list at row 7, column 1, and
since we're only returning one column, the area is 1. I'm going to add a
couple of conveniences before we push, but that's a description of the
naked functions.
To make this more readable, were going to use a name to refer to the
range, now. So we add a column name above the first row of data. and
name the column "colors". Do the usual Calc / Excel naming ceremony to
add a named reference tothe whole column and we can now reference the
column range with its name, 'colors.'
|___A ___|___B___|___C________|____D____|
1 | indigo
2| *colors* *wavelength*
3| infra-red '
4 |red 750
5|orange 620
6|yellow 590
7|green 570
8|blue 495
9| violet 450
And change the formula in A1 to: INDEX( colors, COUNTA(colors), 1, 1 )
Now we can can add additional columns, such as wavelengths (nm) of the
associated color. THis is a table and you can use functions like LOOKUP,
HLOOKUP, MATCH and many others to read and write its cells. Google
searches are helpful if you don't know what an operation is called, like
"curve fitting" or "table functions".
Hopefully this will hepl you make sense of the Calc functions, and I
suspect you may be revising your model, but I didn't really follow what
you were trying to do. If you still want an explanation using that
construct, then I'm going to need an example, I think.
--
*Bill Thoen*
GISnet - www.gisnet.com
303-786-996
_______________________________________________
PLUG mailing list
PLUG@lists.pdxlinux.org
http://lists.pdxlinux.org/mailman/listinfo/plug
