OK, I'm not sure what people mean with "basic" Excel skills but there is a reasonably simple way of doing this. Of course it becomes much more complicated if you're doing repetitive dives but here is a simple example of how to do a simple lookup.
Of course you will have to enter the table into a seperate sheet (as you said) because Excel can't calculate the group but it can look the group up from a table which you provide. You'll then have to understand the MATCH and OFFSET worksheet functions, they're fairly straight forward.
One important thing that will be neccessary is to have the table sorted properly so that Excel can do the lookup. Unfortunately, because we want Excel to find the smallest depth equal or bigger than the maximum dive depth and because we want Excel to find the smallest time equal or greater than the dive time, the table will have to be upside down and inverted left to right from what we are used to with most published dive tables. This is just how the MATCH worksheet function works (third parameter = -1).
Here is a screen grab of a simple table that I quickly entered:
Notice how the table is upside down and left to right.
Now, below the table you will notice three values, one for the dive depth (sorry I used meters
), another for the dive time and a third which is a calculated value for the Pressure Group.
The formula that goes into cell B16 is as follows:
=OFFSET(A1,0,MATCH(B15,OFFSET(B1:L1,MATCH(B14,A2:A11,-1),0),-1))
This might look very daunting but it's actually straight forward if you understand the MATCH and OFFSET worksheet functions. I'll give you a quick rundown of the function above.
Basically, what OFFSET does is to return a cell (or range) which is a specified number of rows and columns removed from a specified cell (or range). What MATCH does is to find a specified value in a range of values and return the position of that value in the range.
So, first of all we want to find the position in the table (top to bottom) that holds the row of time values corresponding to the depth at which the dive was. The function
MATCH(B14,A2:A11,-1) will do that. Basically what we're doing here is to tell Excel to find the position in A2:A11 where the value is equal or greater than B14 (the dive depth).
Now that we know at which position that row is, we need to reference that row. The function
OFFSET(B1:L1,MATCH(B14,A2:A11,-1),0) will do that. Notice how the above MATCH function forms part of this function. What we're doing here is to tell Excel to return the range which is
y number of rows down from B1:L1 and 0 number of columns to the right. Of course the value
y is the result of the first function.
Now that we have the applicable row, we want to find the position of the dive time in it. Again, we can use the MATCH function, as follows:
MATCH(B15,OFFSET(B1:L1,MATCH(B14,A2:A11,-1),0),-1)
This is telling Excel to find the position in
range n where the value is equal or greater than B15 (the dive time).
Finally, now that we have the horizontal position in the row, it's a simple matter of returning the Pressure Group value from the top row, again using the OFFSET function:
=OFFSET(A1,0,MATCH(B15,OFFSET(B1:L1,MATCH(B14,A2:A11,-1),0),-1))
telling Excel to return the cell which is 0 number of rows down from A1 and
x number of columns to the right.
Hope this makes sense. Let me know if you have questions. If you PM me your e-mail address I can send you this Excel file if that would make it easier for you to understand. As a final note, you could also look into using VLOOKUP and HLOOKUP, those are two functions that I use often but for this particular problem I thought the combination of MATCH and OFFSET was easier.
Cheers,
D
But what you've done works out better, good stuff Deefstes!
