Hi,
I am preparing an excel sheet in which I need to determine the time at which the height of the tide will be useful for me.
For example, I have a data set consisting of time (00:00 to 23:00) and the corresponding tide height. (For ex: 0.8m - 1.8m)
In a separate cell, when I enter 1.0, it should do linear interpolation and output the time at which the tide will be 1m.
As you know, the tide changes constantly. Therefore, the required tide height range might be possible 2/ 3 times in a 24 hour span.
I am looking to output all possibilities for the required tide height.
Talking from the attached excel sheet for better understanding now.
Sheet Name : Example.
In here, I have worked out the same requirement for a small portion of time. In cell T15, I will be entering the tide height required and based on the data from S1:X2, it will do interpolation and output the time. The formula for interpolation is in cell T16. But now I need to do the same for a bigger data set, i.e 24 hour span. The info for the same is on Sheet name : Question.
Sheet Name : Question
In here, I have the data from 0:00-23:00. The tide variation is from @0:00 - 1.27 | @4:00 - 0.80 | @11:00 - 1.54 | @15:00 - 1.36 | @20:00 - 1.64 | @23:00 - 1.43 (The highs and lows)
If I need to know the time for when the tide height will be 1.4m, I will have 4 possibilities.
1) 08:00 & 09:00
2) 13:00 & 14:00
3) 16:00 & 17:00
4) 23:00 & 00:00
Based on the above, Is there a way to output the time for all the 4 options in excel and if yes, I would really like to learn.
Thank you for your time and patience.
Kind regards,
Deepak
SHEET NAME : EXAMPLE
SHEET NAME : QUESTION
I am preparing an excel sheet in which I need to determine the time at which the height of the tide will be useful for me.
For example, I have a data set consisting of time (00:00 to 23:00) and the corresponding tide height. (For ex: 0.8m - 1.8m)
In a separate cell, when I enter 1.0, it should do linear interpolation and output the time at which the tide will be 1m.
As you know, the tide changes constantly. Therefore, the required tide height range might be possible 2/ 3 times in a 24 hour span.
I am looking to output all possibilities for the required tide height.
Talking from the attached excel sheet for better understanding now.
Sheet Name : Example.
In here, I have worked out the same requirement for a small portion of time. In cell T15, I will be entering the tide height required and based on the data from S1:X2, it will do interpolation and output the time. The formula for interpolation is in cell T16. But now I need to do the same for a bigger data set, i.e 24 hour span. The info for the same is on Sheet name : Question.
Sheet Name : Question
In here, I have the data from 0:00-23:00. The tide variation is from @0:00 - 1.27 | @4:00 - 0.80 | @11:00 - 1.54 | @15:00 - 1.36 | @20:00 - 1.64 | @23:00 - 1.43 (The highs and lows)
If I need to know the time for when the tide height will be 1.4m, I will have 4 possibilities.
1) 08:00 & 09:00
2) 13:00 & 14:00
3) 16:00 & 17:00
4) 23:00 & 00:00
Based on the above, Is there a way to output the time for all the 4 options in excel and if yes, I would really like to learn.
Thank you for your time and patience.
Kind regards,
Deepak
SHEET NAME : EXAMPLE
Tide Calculator, Rev. 0.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Name | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | ||
2 | Predicted | 1.09 | 1.35 | 1.65 | 1.92 | 2.12 | 2.21 | ||||||
3 | Time Range : 14:00 to 19:00 | ||||||||||||
4 | Time Interpolation at Given Tide | 14:00 | 1.09 | 14:00 | |||||||||
5 | Required Height | 1.30 | 1.09 | 1.35 | 15:00 | 1.35 | 15:00 | ||||||
6 | 14.81 | 14.00 | 15.00 | 16:00 | 1.65 | 16:00 | |||||||
7 | Time | 14:48:36 | 2:00:00 PM | 3:00:00 PM | 17:00 | 1.92 | 17:00 | ||||||
8 | 18:00 | 2.12 | 18:00 | ||||||||||
9 | Tide Interpolation at Given Time : Given Range | 19:00 | 2.21 | 19:00 | |||||||||
10 | Time Input | 15:10 | 15:00 | 16:00 | 20:00 | 2.16 | 20:00 | ||||||
11 | 1.35 | 1.65 | 21:00 | 2.00 | 21:00 | ||||||||
12 | Wave Height | 1.40 | 15:10 | 0 | 22:00 | 1.75 | 22:00 | ||||||
13 | 23:00 | 1.46 | 23:00 | ||||||||||
14 | |||||||||||||
15 | |||||||||||||
Example |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =LOOKUP(E7,B1:G1,B2:G2) |
F5 | F5 | =LOOKUP(F7,C1:L1,C2:L2) |
E6 | E6 | =E7*24 |
F6 | F6 | =E6+1 |
E7 | E7 | =LOOKUP(C5,B2:G2,B1:G1) |
F7 | F7 | =E7+1/24 |
C6 | C6 | =ROUND(E6+(F6-E6)*((C5-E5)/(F5-E5)), 2) |
C7 | C7 | =C6/24 |
E10 | E10 | =LARGE(H4:H13,COUNTIF(H4:H13,">"&C10)+1) |
F10 | F10 | =SMALL(H4:H13,COUNTIF(H4:H13,"<"&C10)+1) |
E11 | E11 | =VLOOKUP(E10,H4:I13,2,FALSE) |
F11 | F11 | =VLOOKUP(F10,H4:I13,2,FALSE) |
E12 | E12 | =C10 |
F12 | F12 | =IFERROR(VLOOKUP(E12,H4:I12,2,FALSE),0) |
C12 | C12 | =IF(F12=0,ROUND(E11+(F11-E11)*((C10-E10)/(F10-E10)), 2),F12) |
J4:J13 | J4 | =H4 |
SHEET NAME : QUESTION
Tide Calculator, Rev. 0.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
2 | TIDAL WAVE PREDICTION - 28 Aug 2021 | ||||||||||||||||||
3 | |||||||||||||||||||
4 | |||||||||||||||||||
5 | Time | Tide | |||||||||||||||||
6 | 0:00 | 1.27 | Tide Interpolation at Given Time : Given Range | Time Interpolation for the required Tide Height | |||||||||||||||
7 | 1:00 | 1.06 | Time Input | 7:37 | 7:00 | 8:00 | Height Input | 1.40 | Tide 1 | Tide 2 | |||||||||
8 | 2:00 | 0.90 | 1.20 | 1.36 | #VALUE! | Time 1 | Time 2 | ||||||||||||
9 | 3:00 | 0.81 | Wave Height | 1.300 | 7:37 | 0 | Time | #VALUE! | |||||||||||
10 | 4:00 | 0.80 | |||||||||||||||||
11 | 5:00 | 0.89 | |||||||||||||||||
12 | 6:00 | 1.03 | |||||||||||||||||
13 | 7:00 | 1.20 | |||||||||||||||||
14 | 8:00 | 1.36 | |||||||||||||||||
15 | 9:00 | 1.48 | |||||||||||||||||
16 | 10:00 | 1.54 | |||||||||||||||||
17 | 11:00 | 1.54 | |||||||||||||||||
18 | 12:00 | 1.50 | |||||||||||||||||
19 | 13:00 | 1.44 | |||||||||||||||||
20 | 14:00 | 1.38 | |||||||||||||||||
21 | 15:00 | 1.36 | |||||||||||||||||
22 | 16:00 | 1.38 | |||||||||||||||||
23 | 17:00 | 1.43 | |||||||||||||||||
24 | 18:00 | 1.51 | |||||||||||||||||
25 | 19:00 | 1.59 | |||||||||||||||||
26 | 20:00 | 1.64 | |||||||||||||||||
27 | 21:00 | 1.63 | |||||||||||||||||
28 | 22:00 | 1.56 | |||||||||||||||||
29 | 23:00 | 1.43 | |||||||||||||||||
Question |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I7 | I7 | =LARGE($C$6:$C$29,COUNTIF($C$6:$C$29,">"&$G$7)+1) |
J7 | J7 | =IFERROR(SMALL($C$6:$C$29,COUNTIF($C$6:$C$29,"<"&$G$7)+1),C6) |
I8 | I8 | =VLOOKUP($I$7,Table1,2,FALSE) |
J8 | J8 | =VLOOKUP($J$7,Table1,2,FALSE) |
I9 | I9 | =G7 |
J9 | J9 | =IFERROR(VLOOKUP($I$9,Table1,2,FALSE),0) |
G9 | G9 | =IF(J9=0,ROUND(I8+(J8-I8)*((G7-I7)/(J7-I7)), 3),J9) |
M8 | M8 | =ROUND(O8+(P8-O8)*((M7-O7)/(P7-O7)), 2) |
M9 | M9 | =M8/24 |