×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Formula

Excel Formula

Excel Formula

(OP)
Can anyone help a rusty engineer write a formula in excell. I want to type a number in a cell which will give an answer in another cell. Sorry if this is pretty basic.

If Cell A = 10 answer in other cell is 30
If Cell A = 11 answer in other cell is 30
If Cell A = 12 answer in other cell is 31
If Cell A = 13 answer in other cell is 31
If Cell A = 14 answer in other cell is 31
If Cell A = 15 answer in other cell is 32
etc, etc.

I asume some form of logical formula should be used but everything i try gives me an error


I haven't used excell since my final year in University approx 15 years ago when I wrote a spreadsheet for moment distribution in continuous beams using the hardy cross method.

Kieran
 

RE: Excel Formula

I think a few more cell data is required. I assume
the second set of values are not absolute but rounded
up numbers.

RE: Excel Formula

By far the easiest way will be to make a table of your potential "A" values and corresponding answers.

Then use a VLOOKUP

 

RE: Excel Formula

How about this
=CHOOSE(A1-9,30,30,31,31,31,32)
iv

RE: Excel Formula

(OP)
Thanks guys for the replies. To give more information, i'm tryng to write a spreadsheet program for calculating the load capacity at any given depth of precast concrete driven piles. The value in the first cell can be any number from 10 to 50. These will be the SPT 'N' values from a geotechnical site investigation. I want to relate these SPT values to the soil angle of friction. The angle of friction for the SPT Values is as follows

SPT N=10, Angle of friction =30
SPT N=20, Angle of friction =33
SPT N=30, Angle of friction =36
SPT N=40, Angle of friction =39
SPT N=50, Angle of friction =41

So i want to enter a figure in one cell (any N value between 10&50) and in another cell get the corresponding angle of friction.  

Kieran
 

RE: Excel Formula

By far the easiest way will be to make a table of your SPY 'N' values and corresponding angles of friction.

Then use a VLOOKUP

RE: Excel Formula

Mintjulep nailed it.  You can create your table in another worksheet and then use "V" or "H" ""Lookup.  Look through  the help for lookup and it will walk you through it.

RE: Excel Formula

How about:

=ROUND(30+(B12-10)*0.3,0)


where B12 is the N value.

or maybe:

=MAX(MIN(ROUND(30+(B12-10)*0.3,0),41),30)

replacing 30 and 41 with whatever you want as your minimum and maximum friction angles.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Excel Formula

You'll have to think of what result you want if the N value is between numbers in your table.  If N=24, do you want the angle to be 33, 36, or something in between?

RE: Excel Formula

Quote:

You'll have to think of what result you want if the N value is between numbers in your table.  If N=24, do you want the angle to be 33, 36, or something in between?  

The OP shows friction angles to the nearest degree.

In this case, where a simple straight line equation fits the data exactly, it's a simple matter to round the result to any desired precision.

In the more general case when you want to interpolate tabular data, that is not a straight line or other specified function, Excel does not provide any built in functions that will do the job in one step.  In that case you can either use the Match and Index functions to get the tabulated values either side of the point you want, and do your own interpolation, or use a UDF, such as those here:
http://newtonexcelbach.wordpress.com/2010/12/07/interpolation-update/

 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Excel Formula

(OP)
Thanks all for your help. The VLOOKUP worked great.

Kieran
 

RE: Excel Formula

isn't there an underlying relationship, ie an equation to relate the data, rather than a table lookup ?

RE: Excel Formula

Quote:

isn't there an underlying relationship, ie an equation to relate the data, rather than a table lookup ?  


Are my posts here not visible for some reason?


How about:
=ROUND(30+(B12-10)*0.3,0)
where B12 is the N value.

or maybe:
=MAX(MIN(ROUND(30+(B12-10)*0.3,0),41),30)
replacing 30 and 41 with whatever you want as your minimum and maximum friction angles.

 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Excel Formula

One accepted relationship for friction angle versus N value:

Phi=3.5*(N)^0.5 + 22.3

That was found in this publication
www.geoengineer.org/files/ISC-zekkosetal2004.pdf

There are equations for the relationships found by Peck-Hanson & by Schmertmann.
I couldn't find direct link for publications with those 2 equations, but Gookle this publication number:
0495668109_247186



 

RE: Excel Formula

N=    50; cell B1
angle=    41; =MIN(B1*3/10+27,B1*2/10+31)


this will give you the values you want for 10<=N<=50

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources