S# Help in a Excel spreadsheet?

Discussion in 'Boat Design' started by Howlandwoodworks, Nov 19, 2020.

  1. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    Hello,
    I am having difficulty with my S# input formula in Excel spreadsheet.
    S# = 3.972 x 10^[-DLR/526 + 0.691 x (log(SAD)-1)^0.8]
    Here is my S# input in Excel below:
    =SUM(3.972*10^(-310.77/526+0.691*(LOG(25.8)-1)^0.8))=2.3
    I am sure this is incorrect but when I add [do this first ] brackets to the calculate a failure error appears in Excel.

    Here are some other numbers that I have conjured up in a spreadsheet:
    SA/D =SUM(345 sf/(3122 lb/64)^0.667)=25.8
    DLR =SUM(3122 lb/2240/(16.5'/100)^3)=310.77
    LOA 27.5'
    LWL 16.5'
    Beam 6.80'
    Draft 3.9166'
    Displacement 3122 lb.

    Thanks to those who share their knowledge freely.
     
  2. fallguy
    Joined: Dec 2016
    Posts: 3,589
    Likes: 512, Points: 123, Legacy Rep: 10
    Location: usa

    fallguy Senior Member

    Share the sheet.

    I am pretty good in Excel, but rather not rebuild your sheet.
     
  3. fallguy
    Joined: Dec 2016
    Posts: 3,589
    Likes: 512, Points: 123, Legacy Rep: 10
    Location: usa

    fallguy Senior Member

    I redid the calc and same result as you.

    You don't have much boat in the water. Are you sure the lwl is correct? 5.5 feet each side out of water seems a bit much. That would be driving a very low S# as the weight of the non-waterline portions could? impact things.

    The dlr, per Sponberg is a moderately heavy cruiser. The sa/d seems high, but possible if you have a lot of sail. Of course, there are only 3 inputs to the S#. Sail area up, displacement down, waterline up raise it...

    Brackets or parenthesis make no difference here except that Excel doesn't like raising bracketed values.
     
    Last edited: Nov 19, 2020
  4. gonzo
    Joined: Aug 2002
    Posts: 14,598
    Likes: 708, Points: 123, Legacy Rep: 2031
    Location: Milwaukee, WI

    gonzo Senior Member

    Nice looking boat.
     
  5. TANSL
    Joined: Sep 2011
    Posts: 6,244
    Likes: 302, Points: 93, Legacy Rep: 300
    Location: Spain

    TANSL Senior Member

    Why not upload your spreadsheet so that an expert can analyze if there are any mistakes in the writing of the formulas?. The truth is that the images shown do not allow to work comfortably.
     
  6. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    Fall Guy,
    I think you are right about water line. The models hull is made out of white pine and the Displacement # I gave you is a african mahogany hull.
    I have thought about flatting the hull out a little for the down wind performance. Making the LWL longer and raising the S#. From what I understand the S# is for the center of gravity directly over the upright center of buoyancy not heeling.
     
  7. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    TANSL

    Yea, you are right about that. That is part of the redesign.
    Here is the way I check the offsets,. You have to do the shear line in another Excel chart.
    Poor folks have poor ways

    upload_2020-11-19_14-3-56.png
     
  8. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    Fall Guy
    The sail area:
    Sail area = Marconi 345 sq. ft. Masthead sloop,
    Gaft 342 sq. ft. adjustable back stays
    Your will be wet one way or the other.
     
  9. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    I have lost my original intent for this thread, sorry my fault. I will start another one on Sailboat design sometime.
    Thanks for the help.
     
    TANSL likes this.
  10. fallguy
    Joined: Dec 2016
    Posts: 3,589
    Likes: 512, Points: 123, Legacy Rep: 10
    Location: usa

    fallguy Senior Member

    I would enjoy seeing the final boat or designs.

    I did verify your disappointing S#!

    I am curious how you arrived at the displacement as well. Boat seems to have plenty..
     
  11. jehardiman
    Joined: Aug 2004
    Posts: 2,804
    Likes: 366, Points: 83, Legacy Rep: 2040
    Location: Port Orchard, Washington, USA

    jehardiman Senior Member

    A couple of items I can think of.
    In the formula do you want the EXCEL function LOG(number,{base}) which is base(10) by default or LN(number) which is log(e)?
    Second, IIRC, both LOG and LN in EXCEL breakdown at very small numbers, but that is not likely the case here.
    Finally, why are you SUM-ming it? The function SUM is is looking for cells to sum.
    And FWIW, in EXCEL syntax the "[text]" is used for named items or ranges of data, so that's why it choked on it.
     
    Howlandwoodworks likes this.
  12. fallguy
    Joined: Dec 2016
    Posts: 3,589
    Likes: 512, Points: 123, Legacy Rep: 10
    Location: usa

    fallguy Senior Member

    He wants the base 10 number based on the desire to get an output from 1-10, unless I am grossly mistaken.
     
    Howlandwoodworks and jehardiman like this.
  13. jehardiman
    Joined: Aug 2004
    Posts: 2,804
    Likes: 366, Points: 83, Legacy Rep: 2040
    Location: Port Orchard, Washington, USA

    jehardiman Senior Member

    Ummm, yes...usually, formulas like this use a "natural" logarithm, not a forced one...(hence the "natural"). Context is everything in code.
     
  14. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    jehardiman,
    I always appreciate your insights. They seem to give me a leg up and a clue as to the next hand hold.

    I am =SUM ing out of habit and it doesn't seem to make any difference. Excel just seems ignore it.

    =(3.972*10^(-310.77/526+0.691*(LOG(25.8)-1)^0.8)) seems to be working.

    Thanks to those who share their knowledge freely.​
     
    Last edited: Nov 20, 2020

  15. Howlandwoodworks
    Joined: Sep 2018
    Posts: 79
    Likes: 17, Points: 18
    Location: Columbia MO

    Howlandwoodworks Member

    Fall Guy,
    Thanks for your interest and help.
    The S# is not disappointing for me. It is a large set of checks and balances and not set in wood yet.
    Diotima is a lead sled inspired by designer from the late 19th and into the early 20th century, hence the low S#. Of course you know that the S# is not everything to do with a boat performance.
    The beauty of ones own Excel spreadsheets is that by changing the leaded keel weight or sail area it will just automatically adjustment the S# in a nano second, and a spreadsheet has a high (Fun to Dollar Ratio). I am not knocking Software packages, I was a licensed with REM/Rate™ as a HERS Rater.
    I have lived much of the more thrilling part my life in boats.
    John Howland

    Although he may not want me to implicate him in my mathematical reckless behavior!

    Eric W. Sponberg
    Naval Architect
    BSE, PE (CT)
    Eng(UK)
    THE DESIGN RATIOS
    A Naval Architect's Dozen (or thereabouts)
    A primer on some basic principles of naval architecture for small crafts
    Has been a great help!

    picture is worth a thousand words.jpg
    "A Picture is worth a thousand words"
    Fred R. Barnard

     
    fallguy likes this.
Loading...
Forum posts represent the experience, opinion, and view of individual users. Boat Design Net does not necessarily endorse nor share the view of each individual post.
When making potentially dangerous or financial decisions, always employ and consult appropriate professionals. Your circumstances or experience may be different.