Help for IF(MAX) expresion

[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 in which direction central cell has maximum slope.

For example: 1 2 3
4 X 5
6 7 8

Using only MAX, I can get maximum slope value but as i mentioned above, I want to in which direction (an in eg.1~8) it has maximum slope value.


IF(A5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),1,IF(B5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),2,IF(C5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),3,IF(A6=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),4,IF(C6=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),5,IF(A7=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),6,IF(B7=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),7,IF(C7=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),8,0))))))))

I have used to formula, where, B6 is the central cell and other are neighboring cells. The result I get with this formula is only 0, which is not correct. Please help.

Thank you

 

[jasonb75]

You're trying to match a cell value to a calculated cell value which is why it's not working, try this version and see if you get the results you're looking for.

IF((B6-A5)/0.707=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),1,
IF((B6-B5)/0.5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),2,
IF((B6-C5)/0.707=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),3,
IF((B6-A6)/0.5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),4,
IF((B6-C6)/0.5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),5,
IF((B6-A7)/0.707=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),6,
IF((B6-B7)/0.5=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),7,
IF((B6-C7)/0.707=MAX((B6-A5)/0.707,(B6-B5)/0.5,(B6-C5)/0.707,(B6-A6)/0.5,(B6-C6)/0.5,(B6-A7)/0.707,(B6-B7)/0.5,(B6-C7)/0.707),8,
0))))))))

I'm sure you can achieve the same results with a much smaller formula, I just haven't figured out how yet.

 

[PIDIT]

Hello Jasonb75,

Thank you very much...it worked.