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
Bookmarks