Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Example:

Given a spreadsheet with four columns - A, B, C, and D, and row 1 is a header row...

A2=1
B2=(Random positive integer)
C2=(Another random positive integer)

A3=A2+1
B3=B2-C2
A4=A3+1
B4=B3-C2

...

D2 is the problem cell. In D2, I want to return the value for column A that matches the first instance of a value in column B that is <=0.

enter image description here

share|improve this question

3 Answers 3

up vote 1 down vote accepted

In a similar vein to Paul's answer, I would use Index and Match

=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)
  1. Use whole columns so it doesn't need to be adjusted for the amount of data in the column.
  2. Use Index rather than Offset since Offset is volatile and Index is not. (Volatile functions recalculate on every sheet calc, while non-volatile functions recalculate only when referenced data change. Too many volatile functions can slow down Excel.)
share|improve this answer
    
I'm not sure why volatile vs. non-volatile is relevant here. Could you explain a bit more? –  Iszi May 9 '12 at 19:19
    
Simply because a lot of volitile functions on a sheet will slow down recalculation. –  chris neilsen May 9 '12 at 19:57
    
That's what I thought, but was wondering if there was some other reason. This seems the most versatile of the options given, and works for me. Thanks! –  Iszi May 9 '12 at 20:01
    
If you want the MATCH to be with the first value <0 then I don't think that will always work, try changing B4 to 0.....this alternative version should work =INDEX($A1:$A100,MATCH(TRUE,INDEX($B1:$B100<=0,0),0)) - that also doesn't rely on column B being sorted desecending –  barry houdini May 9 '12 at 21:13
    
@barryhoudini Maybe suggest that as a separate answer, and give a bit more explanation? For example, I'm confused by your use of TRUE as an argument for MATCH. Also, cell references should still be entire columns, like in chris' example - the size of my data set will vary. –  Iszi May 9 '12 at 21:31

You can use MATCH to get what you need.

MATCH(0,B2:B6,-1)+1 will look through B2:B6 looking for the first value that is equal to or lower than the number 0 and return its relative position. It is indexed from zero, so we add one to match your index.

Indexed from zero means that row B2 is 0, B3 is 1, B4 is 2 and B5 is 3. So MATCH will return "3".

You have your own index in column A, and if this was non-sequential, or contained other values, or didn't start from 1, you could use offset to get to it:

=OFFSET(A2,MATCH(0,B2:B6,-1),0)

So this is using the result of the MATCH to count down from A2 to find the value at that row position.

share|improve this answer

couldn't you calculate that using just B2 and C2?

=CEILING(B2/C2,1)+1

As per my comment in Chris' answer - updated version

=INDEX($A:$A,MATCH(TRUE,INDEX($B:$B<=0,0),0))

This should avoid the problem inherent in Chris' suggestion in that you get the wrong value when the first value <=0 is 0 itself.

The part $B:$B<0 returns an "array" of TRUE/FALSE values, the first TRUE obviously co-inciding with the first column B value <0, MATCH then finds the position of that first instance and INDEX then gets the corresponding value from column A.

The second INDEX is only there to avoid "array entry" - it works without that, i.e.

=INDEX($A:$A,MATCH(TRUE,$B:$B<=0,0))

.......but that version would need to be "array entered" - i.e. confirmed with CTRL+SHIFT+ENTER.

It's a little more inefficient than the previous suggestions, using the whole column (and that doesn't work in Excel 2003 or earlier - in those versions you need to use a specific range).

Note that MATCH with "match type" of -1 as per Chris' suggestion needs to have descending values in column B - this formula works however column B is ordered.

share|improve this answer
    
Way to work around the given problem. I could use this for some applications, but part of the problem with this lies outside the given example - in the real spreadsheet, the decrement changes after a certain point. So, I really need to get the "find first negative" effect. –  Iszi May 9 '12 at 19:33
    
Also, in the real spreadsheet, the data I want returned is non-numerical. Thank you for drawing my attention to essential details I should keep in mind, when asking future questions. –  Iszi May 9 '12 at 20:02
    
OK, apologies, trying to be too clever :( - I posted a comment in Chris' answer..... –  barry houdini May 9 '12 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.