+ Reply to Thread
Results 1 to 23 of 23

Ocean Tides Calculator

  1. #1
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Ocean Tides Calculator

    Good evening everyone,

    Before I begin for the moderators, I will not cross post, I am sorry about the other post, won't happen again.

    Now if you look at my xls file it is for tide calculation for (any giving day/any giving time) with a chart that plots the tide in height and time for both low and high tides. I use the NOW formula seen in cell (A38),which refreshes every time I open the workbook both (date and time). What I wish to do is have this date and time (A38) show on my Tide chart in relation to where it is on the tide level line and it move in relation to cells (A38), in relation to date and time, when I open/refresh the workbook. So I can see where I am at any giving time for that day in the chart during the high and low tides. So basically this date/time will plot itself on the line chart for the tides.

    If changes need to be made to achieve what I would like to be done, please feel free to do so.

    Thank you, Brian
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ocean Tides Calculator

    Hi
    In your graph, select the text box title of axes and in the formula bar type (not in the cell)
    ='Tide Calculation'!$A$38
    see the file
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Thank you for your time but I would like to have this plot itself on the line within the graph between tides and move in relation to CELL (A38)date and time.

  4. #4
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Think of it like this. In between tides it would be a reference point that would reference cell (A38) and display itself on the line inside the chart in between tidal point. So that I would know where I am at any giving time in the tide range.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,366

    Re: Ocean Tides Calculator

    I don't think what you want is possible without some sort of coding, if at all.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ocean Tides Calculator

    Hi
    Try this approach
    In J7:J10 use the following formula (to get the values of x axes)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Use this columns as values of x axes and apply custom format "yyyy-mm-dd hh"
    Note: You can hide the column J
    See the new file
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Thank you too for your time. I already had the time and tide level plotted in the chart. Which is from the "Input Tide data" area CELLS c, d, e, f. I wish to have CELL (A38) which is the current date and time , which is the NOW formula to be DISPLAYED on the line with in the tide chart and as I refresh the worksheet it's precession will change in relation to CELL A38 which is TODAYS DATE AND TIME.

  8. #8
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Does anyone follow what I am saying?
    A single plot for CELL A38 (date and time) which is todays current date and time. So every time I refresh OR open the worksheet the time changes. I want this to be plotted on my tide chart as well. I already have the TIDES level and times plotted.

  9. #9
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    That's what I am looking for a it's called a "Target value". And this "Target value" is from cell A38. How do I plot this in my this scatter plot chart? And also have it refresh as the "Time value" from cell A38 will change, so it can move along the line?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,366

    Re: Ocean Tides Calculator

    Quote Originally Posted by b_rianv View Post
    Does anyone follow what I am saying?
    A single plot for CELL A38 (date and time) which is todays current date and time. So every time I refresh OR open the worksheet the time changes. I want this to be plotted on my tide chart as well. I already have the TIDES level and times plotted.
    Yes, I understand what you want, and as I said before, I doubt it is possible without some coding, if at all.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Ocean Tides Calculator

    AliGW must be better at reading between the lines on this one, because I don't understand what you want.

    How do I plot this in my this scatter plot chart?
    I'm not sure what "this" should be, but the standard approach to plotting anything on a scatter plot is to
    A) Compute the desired X value (=NOW() in your case, if I am understanding correctly)
    B) Compute the desired Y value. Not sure what this should be.
    B1) Maybe you intend this to be along the trendline you have inserted? If so, transfer the coefficients of the trendline to the spreadsheet and use them to compute Y at an X value of A38 or use the LINEST() function to perform the regression in the spreadsheet or use the TREND() function to bypass the coefficients. (list of functions and help files: https://support.office.com/en-us/art...90033e188#bm18 )
    B2) If you intend the plotted point to be elsewhere, then determine how you want to compute Y at A38 and perform that calculation in a convenient cell.
    C) Add these values as a second data series. I prefer to use the Select Data dialog for this action (search help if you do not know where to find the select data dialog in your version of Excel).

    Does that help at all? What part did I misunderstand (if Ali is right and this requires code, then I must have misunderstood something)? What part do you get stuck on?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Ocean Tides Calculator

    I just added this formula to cell G7:

    =NOW()-TODAY()

    and copied down to G10 (formatted as time). Then in H7 I put zero, and the following formulae in the cells stated:

    H8: =H10/3

    H9: =H10/3*2

    H10: =MAX(F7:F10)

    and then added a new series to your graph using the values in G7:G10 as the X-axis and H7:H10 as the Y-axis (coloured yellow to stand out, and removed the markers). This produces the vertical line that I think you are looking for, and it will move along the X-axis whenever the sheet recalculates (or you can press F9).

    Don't know what all the fuss was about !

    Hope this helps.

    Pete

    EDIT: If you want the line to extend above your highest tide, the you can just put the value 3 in H10, although your graph area will adjust and show a value above 3 (3.5 in my case).
    Attached Files Attached Files
    Last edited by Pete_UK; 08-06-2016 at 06:40 PM.

  13. #13
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Yes that's what I was looking for Pete!!! Thank you so much, it work perfectly!!

    Now the last problem I have. If you look at my second sheet (Tide Calendar) you will see the Tides laid out for the month. You will see some days only have (3) tides. when I chart this dat in to my "Input tide data" field my graphic goes crazy, why I don't know. Can someone look at it and tell my why? I believe I have to had another data field for calculating and more area on the graph to plot this or it has something to do with the time of the tides going for one day to the next, "which gives me only "3" tides for that day. The (3) tides days for August 2016 are...Sat 06, Sat 13, Sun 21. High Tides are red, Low Tides are blue.
    Again if you need to add or change something please feel free to.

  14. #14
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Here is the new workbook with Pete's formula I was looking for and the other problem.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Ocean Tides Calculator

    when I chart this dat in to my "Input tide data" field my graphic goes crazy, why I don't know. Can someone look at it and tell my why?
    When I entered the data for 8/6/16 into Tide Calculation, the chart looks normal to me. What do you mean by "goes crazy". When I entered the data into the sheet, I simply hand entered it, clearing the unused row. How do you transfer the data from Tide Calendar to Tide Calculation? When it "goes crazy" what values are in the cells for the "crazy" data point(s)?

    And nice work Pete_UK. I don't know how I did not gather that the OP was looking for a horizontal line to mark "now". Once I see what he was looking for, it seems kind of obvious.

  16. #16
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Good afternoon MrShorty,
    In order to have a full tidal range I have to have (2) low tides and (2) high tides, which you see on the "Tide Calendar for today(08/07/2016) and most of the week. This is great as it will plot a uniformed sine wave in the graph. But on (08/13/2016) there are only (3) tides to plot and the last tides to plot is at 18:06hrs 2.18 high tide, so I have to input the next tide data which is the next tide (08/14/2016) 00:40hrs 0.75 low tide and the graph plots this point behind everything. The sine wave has to be uniformed in order to work and it is not. Please anyone trying to work on this do it. So the next tidal ranges with only (3) tides for a day are 08/13 and 08/21 and 08/27. I forgot to highlight low tides for 08/22 thru 08/26, so there are (4) tides within these days.

    I believe there has to be (5) data points to make this work right, but how???

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Ocean Tides Calculator

    How familiar are you with how Excel stores and uses Date/time data (http://www.cpearson.com/Excel/datetime.htm ). I note that you are entering the times as times only (no date information), which means that the actual values are values between 0 and 1. By understanding this, you should be able to recognize that, to enter the 00:40 time for early Sunday morning so that it fits with Saturdays date, simply enter the time as 24:40 instead of 00:40. Excel may automatically change the displayed time to 00:40 in keeping with the hh:mm time format, but the underlying value will be something slightly larger than 1, so that it "fits" into Saturday's time frame.
    The same thing could be accomplished entering the last tide from the previous day as "minutes before midnight". So, if you wanted to include the last tide from Fri. 12 in your Sat. 13, then you simpley enter =-13/60/24 (which results in a value corresponding to 13 minutes before midnight) and use that for the time value. Excel will not display the time (it will show ####), because Excel's default 1901 date/time system cannot handle negative times, but the chart will look right, because the underlying value is correct.
    Of, if you really need all five points (the last tide from the day before, the three tides on the day of, and the first tide from the day after, simply add another row to the area where you are entering the tide data and enter both the last tide from the day before and the first tide from the day after, as needed. When they are not needed, leave the first or last row blank.
    Is that what you are looking for?
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Thank you for your time sir,the 24:40 approach will work just great. This case is solved!

  19. #19
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ocean Tides Calculator

    Hi
    I made minor corrections to your 'Tide Calendar' in particular the placement of the tides that have their records 11 lines below the date to which they refer. Dates are actual dates and not text.
    To address the issue of the four values to build the chart I had in mind that the missing value refers to the following day tide value.
    I put even auxiliary formulas that enable autofill your 'Tide calculation' table due to the 'Tide Calendar'.
    Two additional notes:
    1) Spaces in the sheet 'Tide Calendar' have code 160 instead of 32.
    2) My system uses the comma as the decimal point, so the formula for converting
    the text "2:32" in value can be simplified by removing the part of error correction
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    can be changed to
    FORMULA]=--MID(J7,12,FIND("ft",J7)-12)[/FORMULA]

    See the file
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    This is wonderful Jose, THANK YOU!! The whole auto updating tide chars is AWESOME!!
    I do have a question, column "L" is showing #VALUE, what is it trying to do?

  21. #21
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Also Jose I forgot to tell you and if you can understand this. This month is for August so the dates of 08/06,08/13,08/21,08/27 are days where there are only (3) tides BUT next month when I do my calculations for tides the days with only (3) tides will change and not be the same dates as they are for this month. Will this effect your formula and how it works?

  22. #22
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ocean Tides Calculator

    Hi
    No problem with my formula. Next month you can use J7:J9 instead J7:J10.
    You can delete K:M formulas (no need for you. I need because 2,13 is a number for me but 2.13 aren't.)
    Try to use in F7
    --MID(J7,12,FIND("ft",J7)-12)
    instead
    =IFERROR(--MID(J7,12,FIND("ft",J7)-12),--(SUBSTITUTE(MID(J7,12,FIND("ft",J7)-12),".",",")))

  23. #23
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Ocean Tides Calculator

    Thank you Jose for everything. I believe the =IFERROR(--MID(J7,12,FIND("ft",J7)-12),--(SUBSTITUTE(MID(J7,12,FIND("ft",J7)-12),".",","))) is working just fine, if not I will try the other one.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Tides Scatter Graph
    By andrewe123 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-20-2016, 05:44 PM
  2. A Hello from the other side of the ocean
    By jlshown in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-19-2013, 12:56 AM
  3. Need help on Ocean Current Stick Plots
    By Onebb in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-15-2012, 05:46 AM
  4. Tax Calculator
    By Birdster in forum Excel General
    Replies: 2
    Last Post: 02-06-2012, 06:50 AM
  5. Integrated calculator in excel 07 instead of separate calculator
    By Wayne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-26-2006, 11:20 AM
  6. [SOLVED] Drowned in the ocean of Excel Spreadsheets
    By Sunantoro in forum Excel General
    Replies: 2
    Last Post: 08-25-2005, 07:05 AM
  7. [SOLVED] calculator
    By Colin2u in forum Excel General
    Replies: 5
    Last Post: 08-19-2005, 04:13 PM

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