Typically complex formula for me

[ms009986]

Hi guys!

I have a row of data, daily stock prices.

I want a formula that'll look in that row for the first value that meets a given condition.

Like...the first value that's 25% more than another value.

or

the first value that's either 25% more than another value, or 10% less than that value.

Etc.

Here's some example data.

$100 $101.50 $98.12 $89.22 $99.50 $107.33 $112.99 $123.20 $124.99 $125.98 $123.10 $131.88​

So let's say I'd want the first value that was 25% more than the initial figure. The formula would give me $125.98.

Or let's say I'd want the first value that was either 25% higher or 10% lower. The formula would give me $89.22. If that low $89.22 hadn't been there, it would have given me the $125.98.

And to throw a wrench in this, sometimes I might want just the value, other times I might just want to know what cell the value is in (so I can count up the number of days it took for an event to occur and so forth).

Okay, smart people...work your magic. It's beyond me.

The Bear of Very Little Brain

 

[Aladin Akyurek]

Hi guys!

I have a row of data, daily stock prices.

I want a formula that'll look in that row for the first value that meets a given condition.

Like...the first value that's 25% more than another value.

or

the first value that's either 25% more than another value, or 10% less than that value.

Etc.

Here's some example data.

$100 $101.50 $98.12 $89.22 $99.50 $107.33 $112.99 $123.20 $124.99 $125.98 $123.10 $131.88​

So let's say I'd want the first value that was 25% more than the initial figure. The formula would give me $125.98.

Or let's say I'd want the first value that was either 25% higher or 10% lower. The formula would give me $89.22. If that low $89.22 hadn't been there, it would have given me the $125.98.

And to throw a wrench in this, sometimes I might want just the value, other times I might just want to know what cell the value is in (so I can count up the number of days it took for an event to occur and so forth).

Okay, smart people...work your magic. It's beyond me.

The Bear of Very Little Brain

Control+shift+enter, not just enter:

A8: 25%

=INDEX(A2:J2,MATCH(TRUE,A2:J2>INDEX(A2:J2,1)*(1+A8),0))

A8 : 25%, B8: 40%

=INDEX(A2:J2,MATCH(1,IF(A2:J2>=INDEX(A2:J2,1)*(1+A8),IF(A2:J2 <= INDEX(A2:J2,1)*(1+A8),1)),0))

 

[Comfy]

Here's a UDF that encompasses all your variables:

Function StockP(rng As Range, Comp As Long, Result As Long) As Variant
    Select Case Result
        Case 1 'First Value More than 25%
            For Each cell In rng.Cells
                If cell.Value > (Comp * 1.25) Then StockP = cell.Value: Exit Function
            Next
        Case 2 'First Value Less than 10% or More than 25%
            For Each cell In rng.Cells
                If cell.Value < (Comp * 0.9) Or cell.Value > (Comp * 1.25) Then StockP = cell.Value: Exit Function
            Next
        Case 3 'Inital + 25% Value
            StockP = Comp * 1.25
        Case 4 'Inital + 25% Address
            For Each cell In rng.Cells
                If cell.Value > Comp * 1.25 Then StockP = cell.Address: Exit Function
            Next
    End Select
End Function

Although I may have misinterpreted what you required for 3 and 4.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

A8: 25%

=INDEX(A2:J2,MATCH(TRUE,A2:J2>INDEX(A2:J2,1)*(1+A8),0))

A8 : 25%, B8: 40%

=INDEX(A2:J2,MATCH(1,IF(A2:J2>=INDEX(A2:J2,1)*(1+A8),IF(A2:J2 <= INDEX(A2:J2,1)*(1+A8),1)),0))

The 2nd formula should be:

=INDEX(A2:J2,MATCH(1,IF(A2:J2>=INDEX(A2:J2,1)*(1+A8),IF(A2:J2 <= INDEX(A2:J2,1)*(1+B8),1)),0))

 

[ms009986]

Okay, Aladin, thanks for the answer. You make it look so easy.

So I sort of got part of it to work...I didn't get the longer formula working right a way so I focused on the shorter one at first, which I modified this way:

=WORKDAY(G6,MATCH(TRUE,P6:ZZ6>=I6*(1+H3*0.01),0))-1

(G6 is the starting date, I6 is the starting price, H3 is the desired percent increase. I use this to get the relative date, and elsewhere I'll use a similar formula with the INDEX part in it to get the actual daily figure out of the cell.)

And that works to give me the date that the condition was met.

Except...

when there's no match to the percent increase, i.e., none of
the data ever got, say, 25% higher than the starting price, the index runs out of data and hits blank cells. When it does, it returns the reference for the first blank cell.

I don't know why it does that...I mean, 0 will never be greater than or equal to, say, $50 * 1.25, or $62.50, but nevertheless it gives back the reference for the first blank cell.

So if you can help me there, maybe that'll make my brain turn over enough to get started again and try to make the more complex formula you gave work for me.



And, Comfy, so thanks for your code...it looks like you're building your own function there, which I didn't know you could do. UDF I'm guessing is user-defined function? Neato. If I can't get this working I'll try to figure out where you add your own functions and give yours a try. I'd have to learn the syntax and stuff for whatever language that is, but I used to fiddle around with programming, so maybe it wouldn't be as daunting as it looks at first glance.



Thanks guys!