Excel Formula

[kieran1]

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

 

[dinjin]

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

 

[Clyde38]

Here are a few examples..

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[MintJulep]

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

Then use a VLOOKUP

 

[Clyde38]

Here is more using MintJulep's idea with data validation. .

If you need more help, you need to better describe how and what you want the formula to do.

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[iv63]

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

 

[kieran1]

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

 

[MintJulep]

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

 

[gbam]

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.

 

[IDS]

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/

 

[jghrist]

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?

 

[IDS]

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/

 

[kieran1]

Thanks all for your help. The VLOOKUP worked great.

Kieran

 

[rb1957]

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

 

[IDS]

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/

 

[CarlB]

One accepted relationship for friction angle versus N value:

Phi=3.5*(N)^0.5 + 22.3

That was found in this publication

http://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

 

[sdz]

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