Cell value when conditions are met

  • Thread starter Thread starter AG
  • Start date Start date
A

AG

I would like a formula to find the value of a cell 1 row before certain
conditions are met.

Beginning with data in row 34 down I want to know the value of the cell in
column J 1 row before the row when these conditions are met for the 1st time:
a. at the 1st occurrence of the value of the cell in column S being > 0
b. the count of items in column N is >= 2
c. the value of column I is either A or B

To explain by example, assume the 1st occurrence of a number > 0 in column S
occurs in row 100.
Prior entries in the range S34:S99 are entered by the text “NA†by design.
The formula would evaluate whether the count of items within the range
N34:N100 is >= 0 and also determine if the value of cell I 100 has either of
the text entries “A†or “B†by design.
If so the formula would have as its result the value of cell J99.

I hope I have explained this clearly enough for both a consideration and a
reply.
Thank you in advance.
 
One crack ..

Try this, array-enter the formula by pressing CTRL+SHIFT+ENTER instead of
just pressing ENTER:
=INDEX(J34:J500,MATCH(1,(ISNUMBER(S34:S500))*(S34:S500>0)*((I34:I500="A")+(I34:I500="B"))*(N34:N500>=2),0)-1)
Adapt the ranges to suit
 
Thank you Max.
The formula works perfectly

Max said:
One crack ..

Try this, array-enter the formula by pressing CTRL+SHIFT+ENTER instead of
just pressing ENTER:
=INDEX(J34:J500,MATCH(1,(ISNUMBER(S34:S500))*(S34:S500>0)*((I34:I500="A")+(I34:I500="B"))*(N34:N500>=2),0)-1)
Adapt the ranges to suit
Click to expand...
 
If I may trouble you again Sir I am trying to understand how your formula
achieved the desired result and learn from your answer.
I tried highlighting the formula’s various factors and pressing the F9 key
to get the result but got stymied with the resultant Excel message that the
“formula is too long.â€

So that I may learn, if you have the time and desire would you please
explain how the formula calculates?
I am familiar with the functions Index and Match but do understand how they
work together with their components in your formula.
 
To avoid this kind of message when diagnosing:
.. "formula is too long."
Click to expand...

Test the formula using small ranges, maybe just 5 rows
(that's what I'd do in framing such formulas up in the 1st place)

Ok, here's some explanations

The earlier formula is basically:
=INDEX(ReturnCol,MATCH(1,(Cond1)x(Cond2)x(Cond3)x...,0)-1)

where
the "-1" in: MATCH(...)-1
is the arithmetic adjustment to return from the row above where the match is
found (that's what you wanted)

The product: (Cond1)x(Cond2)x(Cond3)x...
returns a resultant array of 1's/0's eg: {0;0;1;0;0;0;0;0;0;0;0;...}
depending on where the multi-criteria is satisfied (1's) or not (0's)

MATCH(1,(Cond1)x(Cond2)x(Cond3)x...,0)
matching "1" against the resultant array above hence returns the position
where the 1st match is found (the 1st "1" within the array)

Trust that clarifies it sufficiently

---
 
Just to clarify this line
.. Test the formula using small ranges, maybe just 5 rows ..
Click to expand...

means that instead of using the earlier:
=INDEX(J34:J500, ...
(quite large ranges)

change the expression to use small ranges, eg:
=INDEX(J34:J40,MATCH(1,(ISNUMBER(S34:S40))*(S34:S40>0)*((I34:I40="A")+(I34:I40="B"))*(N34:N40>=2),0)-1)


---
 
I understood completely what you meant earlier.
BTW, I know understand why the formula works; I would say it was a quite
elegant solution to my problem.
Thanks again for the formula & the explanation.
 
Back
Top