0

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

3 Answers 3

1

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.)
6
  • I'm not sure why volatile vs. non-volatile is relevant here. Could you explain a bit more?
    – Iszi
    May 9, 2012 at 19:19
  • Simply because a lot of volitile functions on a sheet will slow down recalculation. May 9, 2012 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, 2012 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 May 9, 2012 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, 2012 at 21:31
1

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.

0

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.

3
  • 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, 2012 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, 2012 at 20:02
  • OK, apologies, trying to be too clever :( - I posted a comment in Chris' answer..... May 9, 2012 at 21:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .