is this possible?

[jpowell79]

Hi Guys,

can anyone tell me if this is possible

Basically there are two cells I wish to reference

Cell P3 (contains values between 0 and 8, and usually counts up in increments of 1...howver it sometimes drops back to zero to start counting up again)

Cell K4 (contains values between -500 and 100)

Now I'm looking for a formula in cell a2 which basically says....

Once P3= 3, then show the value of K4, and then keep showing the value of K4 (regardless of whether or not P3 changes after this) until k4 is equal to or greater than 0, or until p3=3 again


For example

P3=1
K4=-150

therefore A2 = 0


P3=2
k4=-170
therefore A2 = 0


P3=3 (now start monitoring k4)
k4=-310
therefore a2= -310

p3=4
k4=-330
therefore a2= -330

p3=5
k4=-390
therefore a2= -390

p3=0
k4=-100
therefore a2=-100

p3=1
k4=40 (now greater than 0)
therefore a2=0 (reset)

p3=2
k4=-190
therefore a2=0

p3=3 (now start monitoring k4 again)
k4=-230
therefore a2=-230

p3=4
k4=50 (greater than 0)
therefore a2=0


Hopefully this is possible via a formula as macros are no good to me.....long story!!....thanks guys

 

[HalfAce]

Hello jpowell79
Sure, that's not only possible but fairly easy. (Providing I fully understand your requirements.)
(If P3 is greater than or equal to 0 and K4 is less than 0, then show K4, otherwise return a 0.
- that sound about right?)

If so then try this in A2.

=IF(AND(P3>=3,K4<0),K4,0)

 

[jpowell79]

Hi Halface,

that's not exactly what I was looking for....

Basically im looking to get the value of K4 once P3=3

As soon as it is 3, then get k4, even if p3=immediately changes to zero.

For example:

p3=3
k4=-50
a2 should be -50

....but!

if p3 then changes to 0
and k4= -25
a2 should equal -25

The reason is that p3 has not yet reached 3 again, and k4 has not yet reached 0


hope that makes sense

 

[HalfAce]

So... you're basically wanting A2 to remember what the value of P3 was, but may not
be currently?

Off the top of my head I'd have to say you're not going to be able to do that without
some vba.

(You sure that's not an option?)

 

[jpowell79]

here is perhaps an easier way of explaining this....

once p3=3 then switch formula on and make a note of the value of k4

If K4<=0, then show this value.

Keep showing this value now, no matter what p3 has now changed to, until k4>0


once k4>0 then switch off formula and wait until p3=3 again

 

[HalfAce]

Yep, this will require a bit of vba.
How are the values in P3 & K4 getting entered?
(Are these values the returns from formulas?)

If using vba is in fact an option I think you can do this fairly easily.

[EDIT:]
Your last example is a bit confusing as I think it contradicts the earlier description.
But if I understand (which I'm no longer sure I do ) then (again, if any vba at all is
an option - and P3 is a formula) you might try this.
Right click the sheet tab, choose view code and paste this in the sheet module.

Private Sub Worksheet_Calculate()
If [P3] >= 3 Then _
  If [K4] <= 0 Then [A2] = [K4]
If [K4] >= 0 Then [A2] = 0
End Sub

 

[jpowell79]

Hi Halface,

unfortunately no VBA is not an option for this one.....

here is another way of approaching this...

In column P, (in each cell below p3) the worksheet records whether the value of cell P3 is going up or down, each time P3 changes.

For example

P4="up"
p5="up"
p6="up" [this must mean that p3 has equalled 3] as there are 3 consecutive ups.

so basically, if you look at column P, and see "up" in three or more consecutive cells, then p3 must have equalled 3.

so as soon as there is "up" in three consecutive cells, then get the value of K4 until k4 is greater than 0.


so for example:

p4="up"
p5="down"
p6="down"
p7="up"
p8="down"
p9="up"
p10="up"
p11="down"
p12="down"
p13="up"
p14="up"
p15="up" [three consecutive "up" so p3 must equal 3]
p16="down"
p17="down"

so once "up" is displayed in three consecutive cells, then get the value of k4 until k4 is equal to or greater than 0


This is quite complicated i know

 

[Peter_SSs]

jpowell79

As a matter of interest: How are those "up" and "down" values getting into P4, P5, P6 etc?
a) Is there aformula in these cells? If so, what is it?
or
b) Are the "up" and "down" values manually typed in each time?
or
c) Is there macro code putting them there?