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.
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
the second set of values are not absolute but rounded
up numbers.
RE: Excel Formula
RE: Excel Formula
Then use a VLOOKUP
RE: Excel Formula
If you need more help, you need to better describe how and what you want the formula to do.
RE: Excel Formula
=CHOOSE(A1-9,30,30,31,31,31,32)
iv
RE: Excel Formula
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
Then use a VLOOKUP
RE: Excel Formula
RE: Excel Formula
=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
RE: Excel Formula
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://ne
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel Formula
Kieran
RE: Excel Formula
RE: Excel Formula
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
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
angle= 41; =MIN(B1*3/10+27,B1*2/10+31)
this will give you the values you want for 10<=N<=50