S# Help in a Excel spreadsheet?

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.
​

 

Share the sheet.

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

 

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.

 

Nice looking boat.

 

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.

 

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.

 

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

 

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.

 

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.

 

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..

 

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.

 

He wants the base 10 number based on the desire to get an output from 1-10, unless I am grossly mistaken.

 

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

 

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.​

 

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​

​