Slope direction determination

[PIDIT]

Dear all,

I am using Excel, 2007. I want to calculate slope for a central cell (as in eg. X) from 8 neighboring cells. As a result, I want to know if the calculation is positive and if its positive then show maximum slope direction. If its negative show -1 or if its 0 then show 0, instead of direction.
For example: 1 2 3
4 X 5
6 7 8

I used following result. I dont know how to write all those conditions but I tried and it shows error.

=IF(((B5-A4)/0.707>0) and IF((B5-A4)/0.707=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),1,IF((B5-B4)/0.5>0 and IF((B5-B4)/0.5 =MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),2,IF((B5-C4)/0.707>0 and IF((B5-C4)/0.707=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),3,IF((B5-A5)/0.5>0 and IF((B5-A5)/0.5=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),4,IF((B5-C5)/0.5>0 and IF((B5-C5)/0.5=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),5,IF((B5-A6)/0.707>0 and IF((B5-A6)/0.707=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),6,IF((B5-B6)/0.5>0 and IF((B5-B6)/0.5=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),7,IF((B5-C6)/0.707>0 and IF((B5-C6)/0.707=MAX((B5-A4)/0.707,(B5-B4)/0.5,(B5-C4)/0.707,(B5-A5)/0.5,(B5-C5)/0.5,(B5-A6)/0.707,(B5-B6)/0.5,(B5-C6)/0.707),8,0))))))))))))))))

Please help.

Thank you.

 

[pgc01]

Hi

I don't understand what you are trying to do.

Please describe the problem, and, using your example, post the address of the table, the expected result, and what calculations you must to to get at the result.

 

[PIDIT]

Dear PGC01,

Thank you for your response.

Example:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>X</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8</TD></TR></TBODY></TABLE>

I want to to know in which direction does water in central cell (X) will flow. For this I need simple calculation.
Slope= (X-A)/0.5 (where A= 1 or 3 or 6 or 8)

Slope= (X-B)/0.707 (Where B= 2 or 4 or 5 or 7)

As a result, I want to know direction, which should have positive value and maximum value among 8 directions.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>1096</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1078</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1064</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1148</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1122</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1118</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1206</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1196</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1176</TD></TR></TBODY></TABLE>

For example, the result for above is 2, since its positive and maximum among other 7 directions.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>1096</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1078</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1064</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1148</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1206</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1118</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1006</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1196</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1176</TD></TR></TBODY></TABLE>

In this case, the result is -1, since the calculation results negative values in all directions.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>1096</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1078</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>1064</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1148</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1206</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1118</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1206</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1196</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1176</TD></TR></TBODY></TABLE>

In this case result is 0, since maximum postive value is 0.

Please help me. Hope this makes my question clear. Thank you.

 

[mikerickson]

something like
=SIGN(MIN(A1:C3)-C2)
or
=SIGN(MAX(A1:C3)-C2)

??
If the slope is negative, does that mean that the water flows uphill?

 

[mikerickson]

Now that I've had my coffee,

The various slopes are

=(B2-(A1:C3))*{.5,.7,.5 ; .7,0,.7; .5,.7,.5}

so =MAX((B2-(A1:C3))*{.5,.7,.5 ; .7,0,.7; .5,.7,.5}) would be the one you are looking for.

(there formula should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac))

 

[PIDIT]

Dear Mikerickson,

Thank you, but it does not work. It shows #Value!.

If the slope is negative then I need to detect it, thats why I want result for negative slope as -1. Then I will have to increase the value of cell just slighlty higher than lowest neighbouring value in that 3 by 3 window.

Please help.

 

[shg]

Maybe this, confirmed with CSE ...

=MIN((CHOOSE({1,2,3,4,5,6,7,8}, A1,B1,C1,A2,C2,A3,B3,C3) - B2) / SQRT({2,1,2,1,1,2,1,2}))

If the value is positive, water pools at that point.

 

[PIDIT]

Dear All,

Thank you, I fixed the problem.