To Determine the time based on tide height.

D

deepakfer

New Member
Joined
Jun 23, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2013
Platform
  1. Windows
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

Tide Calculator, Rev. 0.xlsx
ABCDEFGHIJK
1Name14:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
2Predicted1.091.351.651.922.122.21
3Time Range : 14:00 to 19:00
4Time Interpolation at Given Tide14:001.0914:00
5Required Height1.301.091.3515:001.3515:00
614.8114.0015.0016:001.6516:00
7Time14:48:362:00:00 PM3:00:00 PM17:001.9217:00
818:002.1218:00
9Tide Interpolation at Given Time : Given Range19:002.2119:00
10Time Input15:1015:0016:0020:002.1620:00
111.351.6521:002.0021:00
12Wave Height1.4015:10022:001.7522:00
1323:001.4623:00
14
15
Example
Cell Formulas
RangeFormula
E5E5=LOOKUP(E7,B1:G1,B2:G2)
F5F5=LOOKUP(F7,C1:L1,C2:L2)
E6E6=E7*24
F6F6=E6+1
E7E7=LOOKUP(C5,B2:G2,B1:G1)
F7F7=E7+1/24
C6C6=ROUND(E6+(F6-E6)*((C5-E5)/(F5-E5)), 2)
C7C7=C6/24
E10E10=LARGE(H4:H13,COUNTIF(H4:H13,">"&C10)+1)
F10F10=SMALL(H4:H13,COUNTIF(H4:H13,"<"&C10)+1)
E11E11=VLOOKUP(E10,H4:I13,2,FALSE)
F11F11=VLOOKUP(F10,H4:I13,2,FALSE)
E12E12=C10
F12F12=IFERROR(VLOOKUP(E12,H4:I12,2,FALSE),0)
C12C12=IF(F12=0,ROUND(E11+(F11-E11)*((C10-E10)/(F10-E10)), 2),F12)
J4:J13J4=H4




SHEET NAME : QUESTION

Tide Calculator, Rev. 0.xlsx
CDEFGHIJKLMNOPQRS
2TIDAL WAVE PREDICTION - 28 Aug 2021
3
4
5TimeTide
60:001.27Tide Interpolation at Given Time : Given RangeTime Interpolation for the required Tide Height
71:001.06Time Input7:377:008:00Height Input1.40Tide 1Tide 2
82:000.901.201.36#VALUE!Time 1Time 2
93:000.81Wave Height1.3007:370Time#VALUE!
104:000.80
115:000.89
126:001.03
137:001.20
148:001.36
159:001.48
1610:001.54
1711:001.54
1812:001.50
1913:001.44
2014:001.38
2115:001.36
2216:001.38
2317:001.43
2418:001.51
2519:001.59
2620:001.64
2721:001.63
2822:001.56
2923:001.43
Question
Cell Formulas
RangeFormula
I7I7=LARGE($C$6:$C$29,COUNTIF($C$6:$C$29,">"&$G$7)+1)
J7J7=IFERROR(SMALL($C$6:$C$29,COUNTIF($C$6:$C$29,"<"&$G$7)+1),C6)
I8I8=VLOOKUP($I$7,Table1,2,FALSE)
J8J8=VLOOKUP($J$7,Table1,2,FALSE)
I9I9=G7
J9J9=IFERROR(VLOOKUP($I$9,Table1,2,FALSE),0)
G9G9=IF(J9=0,ROUND(I8+(J8-I8)*((G7-I7)/(J7-I7)), 3),J9)
M8M8=ROUND(O8+(P8-O8)*((M7-O7)/(P7-O7)), 2)
M9M9=M8/24
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Welcome to the Forum!

With 365, you could do it like this:

ABCDE
1
2TimeTide
324 Jun 00:001.27RequiredTideTimes
424 Jun 01:001.061.424 Jun 08:20
524 Jun 02:000.9024 Jun 13:40
624 Jun 03:000.8124 Jun 16:24
724 Jun 04:000.8025 Jun 00:15
824 Jun 05:000.89
924 Jun 06:001.03
1024 Jun 07:001.20
1124 Jun 08:001.36
1224 Jun 09:001.48
1324 Jun 10:001.54
1424 Jun 11:001.54
1524 Jun 12:001.50
1624 Jun 13:001.44
1724 Jun 14:001.38
1824 Jun 15:001.36
1924 Jun 16:001.38
2024 Jun 17:001.43
2124 Jun 18:001.51
2224 Jun 19:001.59
2324 Jun 20:001.64
2424 Jun 21:001.63
2524 Jun 22:001.56
2624 Jun 23:001.43
2725 Jun 00:001.41
2825 Jun 01:001.37
Question
Cell Formulas
RangeFormula
E4:E7E4=LET(t,IF(SIGN((StartTide-RequiredTide)*(EndTide-RequiredTide))=1,0,IFERROR(((RequiredTide-StartTide)*EndTime+(EndTide-RequiredTide)*StartTime)/(EndTide-StartTide),StartTime)),UNIQUE(FILTER(t,t<>0,"No result")))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
EndTide=Question!$B$4:$B$28E4
EndTime=Question!$A$4:$A$28E4
RequiredTide=Question!$D$4E4
StartTide=Question!$B$3:$B$27E4
StartTime=Question!$A$3:$A$27E4
 
Upvote 0
Solution
Dear Mr. Stephen,

Thank you very much for your reply.

In the office, we have only 2013. Is there a solution, may be through VBA to do the same?

Meanwhile, I will check this solution at home on my laptop and revert incase of any troubles.

Much appreciated your response :)

Thanks & Regards,
Deepak Fernandes
 
Upvote 0
deepakfer said:
In the office, we have only 2013.
Click to expand...
Using the same layout as above:

E4: =IFERROR(SMALL(IF(SIGN((StartTide-RequiredTide)*(EndTide-RequiredTide))<1,IFERROR(((RequiredTide-StartTide)*EndTime+(EndTide-RequiredTide)*StartTime)/(EndTide-StartTide),StartTime)),ROWS(E$4:E4)),"")

You may get some duplicates, e.g. try with RequiredTide=1.54 and you'll see what I mean. That's why I used UNIQUE() in the 365 formula.

The formula can probably be expanded to eliminate these, but it would become unwieldy.
 
Upvote 0
Thank you very much Mr. Stephen.

The above formula works like a charm. What I did is I input the INDEX & MATCH Formula in the next cell to filter out the unique values from the formulated data set and that did the trick.

Your formula : {=IFERROR(SMALL(IF(SIGN((StartTide-RequiredTide)*(EndTide-RequiredTide))<1,IFERROR(((RequiredTide-StartTide)*EndTime+(EndTide-RequiredTide)*StartTime)/(EndTide-StartTide),StartTime)),ROWS(O$6:O6)),"")}

Additional Formula to Filter Unique Values
{=IFERROR(INDEX($O$6:$O$21, MATCH(0,COUNTIF($M$24:M24, $O$6:$O$21), 0)),"")}.

I appreciate you for taking the time and helping me out here.

I wanna try the same on the Office365 formula that you posted first and will respond later.

Cheers!

Kind regards,
Deepak
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

With 365, you could do it like this:

ABCDE
1
2TimeTide
324 Jun 00:001.27RequiredTideTimes
424 Jun 01:001.061.424 Jun 08:20
524 Jun 02:000.9024 Jun 13:40
624 Jun 03:000.8124 Jun 16:24
724 Jun 04:000.8025 Jun 00:15
824 Jun 05:000.89
924 Jun 06:001.03
1024 Jun 07:001.20
1124 Jun 08:001.36
1224 Jun 09:001.48
1324 Jun 10:001.54
1424 Jun 11:001.54
1524 Jun 12:001.50
1624 Jun 13:001.44
1724 Jun 14:001.38
1824 Jun 15:001.36
1924 Jun 16:001.38
2024 Jun 17:001.43
2124 Jun 18:001.51
2224 Jun 19:001.59
2324 Jun 20:001.64
2424 Jun 21:001.63
2524 Jun 22:001.56
2624 Jun 23:001.43
2725 Jun 00:001.41
2825 Jun 01:001.37
Question
Cell Formulas
RangeFormula
E4:E7E4=LET(t,IF(SIGN((StartTide-RequiredTide)*(EndTide-RequiredTide))=1,0,IFERROR(((RequiredTide-StartTide)*EndTime+(EndTide-RequiredTide)*StartTime)/(EndTide-StartTide),StartTime)),UNIQUE(FILTER(t,t<>0,"No result")))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
EndTide=Question!$B$4:$B$28E4
EndTime=Question!$A$4:$A$28E4
RequiredTide=Question!$D$4E4
StartTide=Question!$B$3:$B$27E4
StartTime=Question!$A$3:$A$27E4
Click to expand...
StephenCrump said:
Welcome to the Forum!

With 365, you could do it like this:

ABCDE
1
2TimeTide
324 Jun 00:001.27RequiredTideTimes
424 Jun 01:001.061.424 Jun 08:20
524 Jun 02:000.9024 Jun 13:40
624 Jun 03:000.8124 Jun 16:24
724 Jun 04:000.8025 Jun 00:15
824 Jun 05:000.89
924 Jun 06:001.03
1024 Jun 07:001.20
1124 Jun 08:001.36
1224 Jun 09:001.48
1324 Jun 10:001.54
1424 Jun 11:001.54
1524 Jun 12:001.50
1624 Jun 13:001.44
1724 Jun 14:001.38
1824 Jun 15:001.36
1924 Jun 16:001.38
2024 Jun 17:001.43
2124 Jun 18:001.51
2224 Jun 19:001.59
2324 Jun 20:001.64
2424 Jun 21:001.63
2524 Jun 22:001.56
2624 Jun 23:001.43
2725 Jun 00:001.41
2825 Jun 01:001.37
Question
Cell Formulas
RangeFormula
E4:E7E4=LET(t,IF(SIGN((StartTide-RequiredTide)*(EndTide-RequiredTide))=1,0,IFERROR(((RequiredTide-StartTide)*EndTime+(EndTide-RequiredTide)*StartTime)/(EndTide-StartTide),StartTime)),UNIQUE(FILTER(t,t<>0,"No result")))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
EndTide=Question!$B$4:$B$28E4
EndTime=Question!$A$4:$A$28E4
RequiredTide=Question!$D$4E4
StartTide=Question!$B$3:$B$27E4
StartTime=Question!$A$3:$A$27E4
Click to expand...
What does the t stands for in the formula? right after LET
 
Upvote 0
@Heritage1973, welcome to the forum!

Heritage1973 said:
What does the t stands for in the formula? right after LET
Click to expand...
"t" is just the arbitrary name I have given to the formula:

=IF(SIGN((starttide-RequiredTide)*(endtide-RequiredTide))=1,0,IFERROR(((RequiredTide-starttide)*endtime+(endtide-RequiredTide)*starttime)/(endtide-starttide),starttime))

Having defined t in this way, the result I wanted was: UNIQUE(FILTER(t,t<>0,"No result")) - using t means I don't need to double-enter the same formula.

Have a look at how the LET function works, e.g. Dave Bruns's Exceljet website usually explains things really clearly: LET Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
T
  • Question Question
Time Sheet question
2
Replies
15
Views
904
tfowlerusa
T
H
  • Question Question
To Determine the time based on tide height.
Replies
4
Views
696
robertvdb
R
K
  • Question Question
Roster lookup table - v/h/x lookup?
Replies
2
Views
323
Kariba
K
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top