At 23:04 28/09/2008 +0300, Andis Noname wrote:
I'm looking if there is easy possibility to chose variable from the
table, like in example below, to use in calculations is another table.
I have table like this, but much longer, to chose variables from:
*Average height (m)* *Aspen* *Grey alder* *Birch*
1 2000 2000 2000
2 1800 1800 1800
3 1600 1600 1600
4 1600 1600 1600
5 1600 1600 1600
6 1600 1600 1600
7 1500 1600 1500
8 1400 1500 1400
9 1300 1500 1300
10 1200 1500 1200
11 1100 1300 1100
And I have another table with the results of forest inventory, which
contains columns specie and average height, besides other 245
variables. I need additional column with some kind of lookup field,
where according to a specie and average tree height I can see
regulatory numbers of trees (values in the first table). For
instance if the main specie is Aspen and average height of trees is
10 m, regulatory number of trees should be 1200.
What are the options to do it, preferably using array functions?
I don't think you need an array function here, in fact. Your problem
is made easier by the fact that the first column is a regular
sequence, not random values. You can use the HLOOKUP() function to
search the column headers for the correct species and then use the
average height directly to choose the row from which the result
should be taken. Suppose that your data table in is cells A1 to D12
and that the species, average height, and your required number in the
other table are in columns G, H, and I. In I1, put:
=HLOOKUP(G1;B$1:D$12;H1+1;FALSE)
Here, B1:D12 is the array of values in your table, G1 is the species
value you are looking for - Aspen in your example - and H1 is the
average height value. The function searches the first row - the
column headers - for your required species, and then returns the
value in the specified row of that column. Since your average
heights have 1 in row 2, 2 in row 3, and so on, you need H1+1 to
specify the required row: 10 in H1 gives 11 as the required row. The
final "FALSE" is necessary to indicate that the column headers are
not sorted. The two $ signs are not necessary for the formula to
work, but will be - to prevent the table reference changing - if you
wish to copy the formula down the relevant column (I). A good
alternative is to give your data table a name and reference it by
that rather than actual cell identifiers.
Oh, and I found that column names with asterisks confused the
function, so you'd be better off without these.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
