Results 1 to 9 of 9

Looking up a maximum value in a column where the columns vary in the number of rows

Threaded View

  1. #1
    Registered User
    Join Date
    02-15-2010
    Location
    Paris, Ontario, Canada
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Looking up a maximum value in a column where the columns vary in the number of rows

    First off, I'm an absolute newb to what I'm attempting to do and secondly I've tried to sift through some of the posts but just can't seem to find what I'm after. I apologize if this is covered elsewhere.

    I'm trying to create a spreadsheet for dive planning. I enter a target depth in cell E17 and have the sheet round the value based on the criteria for dive planning as below:

    IF(E17<=35,35,ROUNDUP(E17,-1))

    So, if my target depth is under 35 feet, the value becomes 35 feet, and any intermediate value higher than 35 feet that is not a multiple of ten is rounded to the next higher value. No issue so far. Now that I have my rounded value the fun (or in my case headache) begins.

    This is what my first table looks like. Depths for column headers, pressure groups for row headers. I've tried HLOOKUP and INDEX MATCH combinations. The issue is needing to get to the appropriate depth column and go down to the maximum time value and output that into a cell. Also I'd like to go across from that time value and return the lettered pressure group to another cell. Finally I'd like to be able to enter a target time and find that value or the next one higher in the depth column and return the lettered pressure group (which typically won't be the maximum value) for the target time. I'm sure if I can figure out how to get this table to work I can get the other parts of the sheet to function.

    35 40 50 60 70 80 90 100 110 120 130 140
    A 10 9 7 6 5 4 4 3 3 3 3 ↓
    B 19 16 13 11 9 8 7 6 6 5 5 4
    C 25 22 17 14 12 10 9 8 7 6 6 5
    D 29 25 19 16 13 11 10 9 8 7 7 6
    E 32 27 21 17 15 13 11 10 9 8 ↓ 7
    F 36 31 24 19 16 14 12 11 10 9 8 8
    G 40 34 26 21 18 15 13 12 11 10 9
    H 44 37 28 23 19 17 15 13 12 11 10
    I 48 40 31 25 21 18 16 14 13 ↓
    J 52 44 33 27 22 19 17 15 ↓ 12
    K 57 48 36 29 24 21 18 16 14 13
    L 62 51 39 31 26 22 19 17 15
    M 67 55 41 33 27 23 21 18 16
    N 73 60 44 35 29 25 22 19
    O 79 64 47 37 31 26 23 20
    P 85 69 50 39 33 28 24
    Q 92 74 53 42 35 29 25
    R 100 79 57 44 36 30
    S 108 85 60 47 38
    T 117 91 63 49 40
    U 127 97 67 52
    V 139 104 71 54
    W 152 111 75 55 Safety Stop
    X 168 120 80 Required
    Y 188 129
    Z 205 140

    My main issue is that most functions and examples seem to expect the same number of rows for each column but my columns are not all equal in the number of rows.

    Any insight appreciated.

    EDIT: I copy pasted from my table and looks fine when composing or editing, but looks jumbled when posted, sorry,
    Jason
    Last edited by jkuetemann; 02-15-2010 at 04:45 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1