Delete [EMAIL PROTECTED] from your list.
----- Original Message ---- From: Brian Barker <[EMAIL PROTECTED]> To: [email protected] Sent: Sunday, September 28, 2008 6:29:29 PM Subject: Re: [users] Is there Calc function to chose variable from the table on the base of 2 search criteria in the first row and the column? 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]
