Formula that return the difference in a set of numbers closest to it

[lar9149]

Hello all

2 weeks ago on was looking for a formula that returned 0,-1,+1 or blank depending on different scenarios..below is the thread for reference

https://www.excelforum.com/excel-for...ml#post5514002

I would actually like to modify that formula now that instead of returning blanks it would return the difference of the number closest to it in value in the comparison row (in the example row 4(the 0,-1,+1 will still be the same formula). In the example I put the numbers in red & green (rows 10/11) with row 9 containing the original formula. In green I put what I am looking to have instead of the blank.

In the example, row 11 column B would return a +3 which corresponds to 15 above it & would take the difference of the closest number in row 4 which is 18. In columns E-G the same thing.

As far as whether the difference would be taken from the number on row 4 or row 11 (for example, in the above it takes the difference of 18-15 which is +3 rather than 15-18 which is -3) will depend on which number is bigger in column A in the corresponding rows (in the case of the numbers being equal it will take the next column B, if those are equal column C & so on)

In this case row 4 column A is 13 while row 10 column A is 12 so it will take 18-15 and the same for the others.

So far the formula I taught about but unable to complete will replace the formula say in row 11 column A "" with something else

=IFNA(LOOKUP(1,0/COUNTIFS($A$4:$I$4,A10+{1,0,-1}),{1,0,-1}),"")

And it would contain perhaps with a if statement that starts like

OR(AND($A$4=$A11,$B$4=$B11,$C$4>$C11),AND($A$4=$A11,$B$4>B11),$A$4>$A11), then take the difference of the corresponding value in row 4 from 11.
Than an opposite scenario also
OR(AND($A$4=$A11,$B$4=$B11,$C$4<$C11),AND($A$4=$A11,$B$4<B11),$A$4<$A11),then take the difference of the corresponding value in row 11 from 4


Of course than maybe a better way to approach. Any help would be appreciated. Thank you.

[Bo_Ry]

No idea why you choose
18-15 = 3 and 22-25 = -3 instead of closer number
13-15 = -2 and 27-25 = 2

This formula is to get the closest number.
A9
=LOOKUP(1,1/FREQUENCY(0,ABS($A$4:$I$4-A8)),$A$4:$I$4)-A8

[lar9149]

actually that works to replace the original formula..thank you for your help