# is this possible?

#### jpowell79

##### Active Member
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 ### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### HalfAce

##### MrExcel MVP
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.

If so then try this in A2.
Code:
``=IF(AND(P3>=3,K4<0),K4,0)``

#### jpowell79

##### Active Member
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

##### MrExcel MVP
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

##### Active Member

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

##### MrExcel MVP
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.
Code:
``````Private Sub Worksheet_Calculate()
If [P3] >= 3 Then _
If [K4] <= 0 Then [A2] = [K4]
If [K4] >= 0 Then [A2] = 0
End Sub``````

#### jpowell79

##### Active Member
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

##### MrExcel MVP, Moderator
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?

Replies
4
Views
119
Replies
2
Views
161
Replies
0
Views
152
Replies
3
Views
319
Replies
3
Views
262

### Forum statistics

1,141,160
Messages
5,704,649
Members
421,360
Latest member
Rhodia ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back