Lower than -10% give 1, then give 2.

**tsakta13ole** · 06-07-2016, 09:23 AM

Hi. I'm attaching my workbook. I have a column that has numbers. I would like a formula or macro that will give me the following: If the number in column A is lower than -10% give me 1. Then to get a 2 it needs to have risen +10% from when it got a 1.

An example to understand:

Column A and B
-3%
-12% 1
-4%
-14%
-13%
-1% 2
3%
1%
-13% 1
3% 2
10%
-17% 1
-16%
-15%
6% 2
5%
6%
Starting from A1=-3%. We are looking for a number that is lower than -10%. A2 is below -10%. So, B2 will be 1. Now we are looking for a 10% rise from when we got in. This happens at A6 so B6 will be 2. Whenever we get a 1 it has to stop looking for a -10% value. That's why B4 and B5 are not 1. So B6 is 2. Now, we start looking for a number lower than -10% again. We get this at A9. B9 has to be 1. The 10% rise happens at row 10. So B10 will be 2.

Hope you understand what I'm trying to do!
Thanks for any help!

P.S.: I have posted the same question in another forum http://www.mrexcel.com/forum/excel-q...ml#post4543231

**zbor** · 06-07-2016, 09:43 AM

Here, try this (starting in second row, B1 is 1 by default):

**tsakta13ole** · 06-07-2016, 10:02 AM

Thank you for your answer. The results are the same but when I change the data it doesn't do what I would like. I'm attaching my workbook.

In sheet 2 when I change the data B33 is 2 which it should not be. Also, after a 1 there should be a 2. B58 is 1 and the next non-blank is again 1 at B97.

Thanks for any help!

**tsakta13ole** · 06-07-2016, 11:37 AM

I got the answer in the other forum. This

=IF(COUNTIF($B$1:$B1,"=1")-COUNTIF($B$1:$B1,"=2")=0,IF(A2<-0.1,1,""),IF(COUNTIF($B$1:$B1,"=1")-COUNTIF($B$1:$B1,"=2")=1,IF(A2-0.1>= INDEX($A$1:$A1,SUMPRODUCT(MAX(ROW($B$1:$B1)*($B$1:$B1=1)))),2,"")))

and this

Sub MG07Jun52
Dim Rng As Range, Dn As Range, Num As String
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Num = "" Then
            If Dn.Value < -0.1 Then
                 Dn.Offset(, 1) = 1
                Num = Dn.Value
            End If
    ElseIf Not Num = "" Then
            If Dn.Value >= Num + 0.1 Then
                Dn.Offset(, 1) = 2
                Num = ""
            End If
    End If
Next Dn
End Sub

**zbor** · 06-07-2016, 01:48 PM

Previous formula looked did value grow or not.
If you want to alternate 1 and 2 no matter of growth then:

in B1 put value 2 (so next value will start with 1)
in B2 put:

Formula:

=IF(ABS(LOOKUP(2;1/(B$1:B1<>"");A$1:A1)-A2)>0,1;1+MOD(LOOKUP(99^99;B$1:B1);2);"")

And extend down.

(Change ; to , if you get error)

Edit: Thank you for putting crosspost link.

**tsakta13ole** · 06-07-2016, 02:15 PM

Thanks for your effort zbor. This does not quite do what I would like. For example, I get a 1 at B33 which is above 10%, not below 10% and then I get a 2 when it falls 10% from that level, not rise.
Anyway, I got the answer in the other forum and posted it here. No need for another answer!

Thanks again for your effort!

Lower than -10% give 1, then give 2.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Lower than -10% give 1, then give 2.

Re: Lower than -10% give 1, then give 2.

Re: Lower than -10% give 1, then give 2.

Re: Lower than -10% give 1, then give 2.

Re: Lower than -10% give 1, then give 2.

Re: Lower than -10% give 1, then give 2.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] why give me error #VALUE ... i need to give me the numbers from small to big

[SOLVED] Want excel to give a combination of a few different numbers with give same figure as sum

give n/a value

[SOLVED] If Statements (?) - If Cell1 =yes then give -Cell2, If Cell1=no then give +Cell2

[SOLVED] Give RELEVANT responses to questions. DO NOT give usless list

I give up! Help Please...

Bookmarks

Bookmarks

Posting Permissions