Good morning everyone!
Another wonderful workday to troubleshoot excel! Here's what I'm having difficulty with (see attached).
I want to take the values in column L (in purple), and find the difference between its nearest neighbors. Any ideas?
Thanks in advance guys!
hi there. try this in M2:
=LOOKUP(L2,A2:J2)-L2
and this in N2:
=INDEX(A2:J2,MATCH(TRUE,INDEX(A2:J2>=L2,),0))-L2
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Will there always be a negative result and a positive result (or 0)?
These array formulas** work on the limited sample data in your file:
For the lower diff:
=MAX(IF(A2:J2-L2<=0,A2:J2-L2))
For the upper diff:
=MIN(IF(A2:J2-L2>=0,A2:J2-L2))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
I have a feeling that it's more complicated than this!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@ Tony Valko; no, the values don't have to be either negative or positive. I will know myself.
In that case disregard my suggestions.
Will there always be a value less than or equal to the target value?
Will there always be a value greater than or equal to the target value?
Sorry I should've been much more specific.
The values in the green columns will always be positive. However the returned values from the lower columns don't necessarily have to be negative or positive (even though they are in actuality).
For the lowest values in the green columns, the L column values could be lower, in which case bring back the difference from the nearest top neighbor, and likewise at the other end of the green column, bringing back the difference from the nearest neighbor below it.
Can you post a new sample file with more examples of the results you expect?
About 20 rows worth of data should be plenty.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks