# S# Help in a Excel spreadsheet?

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

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

### HowlandwoodworksMember

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. Joined: Dec 2016
Posts: 3,589
Likes: 512, Points: 123, Legacy Rep: 10
Location: usa

### fallguySenior Member

Share the sheet.

I am pretty good in Excel, but rather not rebuild your sheet.

3. Joined: Dec 2016
Posts: 3,589
Likes: 512, Points: 123, Legacy Rep: 10
Location: usa

### fallguySenior 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. Joined: Aug 2002
Posts: 14,598
Likes: 708, Points: 123, Legacy Rep: 2031
Location: Milwaukee, WI

### gonzoSenior Member

Nice looking boat.

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

### TANSLSenior 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. Joined: Sep 2018
Posts: 79
Likes: 17, Points: 18
Location: Columbia MO

### HowlandwoodworksMember

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. Joined: Sep 2018
Posts: 79
Likes: 17, Points: 18
Location: Columbia MO

### HowlandwoodworksMember

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

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

### HowlandwoodworksMember

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. Joined: Sep 2018
Posts: 79
Likes: 17, Points: 18
Location: Columbia MO

### HowlandwoodworksMember

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. Joined: Dec 2016
Posts: 3,589
Likes: 512, Points: 123, Legacy Rep: 10
Location: usa

### fallguySenior 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. Joined: Aug 2004
Posts: 2,804
Likes: 366, Points: 83, Legacy Rep: 2040
Location: Port Orchard, Washington, USA

### jehardimanSenior 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. Joined: Dec 2016
Posts: 3,589
Likes: 512, Points: 123, Legacy Rep: 10
Location: usa

### fallguySenior 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. Joined: Aug 2004
Posts: 2,804
Likes: 366, Points: 83, Legacy Rep: 2040
Location: Port Orchard, Washington, USA

### jehardimanSenior Member

Ummm, yes...usually, formulas like this use a "natural" logarithm, not a forced one...(hence the "natural"). Context is everything in code.

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

### HowlandwoodworksMember

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. Joined: Sep 2018
Posts: 79
Likes: 17, Points: 18
Location: Columbia MO

### HowlandwoodworksMember

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!

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

fallguy likes this.
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.