Cell value when conditions are met
 

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.

Cell value when conditions are met
 

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: S5000)*((I34:I500="A")+(I34:I500="B"))*(N34:N500 =2),0)-1)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
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.

Cell value when conditions are met
 

Thank you Max.
The formula works perfectly

"Max" wrote:

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: S5000)*((I34:I500="A")+(I34:I500="B"))*(N34:N500 =2),0)-1)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
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.

Cell value when conditions are met
 

Welcome, AG. Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
Thank you Max.
The formula works perfectly

Cell value when conditions are met
 

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 formulas 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.


"Max" wrote:

Welcome, AG. Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
Thank you Max.
The formula works perfectly

Cell value when conditions are met
 

To avoid this kind of message when diagnosing:
.. "formula is too long."

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
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.

Cell value when conditions are met
 

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

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:S4 00)*((I34:I40="A")+(I34:I40="B"))*(N34:N40=2),0 )-1)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Cell value when conditions are met
 

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.


"Max" wrote:

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

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:S4 00)*((I34:I40="A")+(I34:I40="B"))*(N34:N40=2),0 )-1)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Cell value when conditions are met
 

Glad to hear that, AG.
Thanks for posting back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
I understood completely what you meant earlier.
BTW, I now understand why the formula works; I would say it was a quite
elegant solution to my problem.
Thanks again for the formula & the explanation.