0 views

Skip to first unread message

Jul 22, 2009, 5:07:01 PM7/22/09

to

Hi there,

You'll have to forgive me because i am a bit of a novice wiith respect to

vba but i am trying to learn it...rather painfully.

So here's the problem:

I have a column of data: example below:

COLUMN A COLUMN B

Last Price

1.64500

1.64520

1.64520

1.64540

1.64550

1.64550

1.64520

1.64570

1.64530

1.64510

1.64540

1.64550

1.64540

1.64540

1.64530

1.64500

1.64520

1.64560

1.64540

1.64580

1.64590

1.64570

1.64560

1.64550

1.64540

1.64520

1.64580

1.64580

1.64570

1.64600

1.64580

1.64600

1.64640

1.64650

1.64630

1.64650

1.64710

1.64710

1.64680

1.64690

1.64660

1.64650

1.64630

1.64640

1.64620

1.64640

1.64630

1.64620

1.64650

1.64650

1.64660

1.64640

1.64630

1.64640

1.64670

1.64690

1.64720

1.64720

1.64680

1.64680

1.64700

In "Column B" i want to do the following:

Step 1:

If the value in cell A2 is greater than or equal to the value in cell A5

assign value = 1

If the value in cell A2 is less than or equal to the value in cell A5 assign

value = -1

*at this point I realise that there is an illogical step (if the value is

the same, value according to the statement above will be equal to both 1 and

-1) step 2 explains what to do in this situation.

Step 2:

So we are currently calulating the relative 1's and -1’s as we descend the

column.

I now need to sum these cumulatively. See example below:

If i have 1,1,1,1,1 the formula needs to sum them as it "counts" > (showing

1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g.

1,1,1,1,-1,-1,-1,1,1,) it just starts the cumulative count again at zero >

(showing 1,2,3,4,-1,-2,-3,1,2). So:

Example 1: All positive 1’s”

(count) I would want the number to add to each other each time

(sum cumulatively)

1 1

1 2

1 3

1 4

1 5

1 6

1 7

1 8

1 9

Example 2: All negative -1’s”

(count) I would want the number to add to each other each time

(sum cumulatively)

-1 -1

-1 -2

-1 -3

-1 -4

-1 -5

-1 -6

-1 -7

-1 -8

-1 -9

Example 3: IMPORTANT: IF POSITIVE AND NEGATIVE 1/-1’s

(count) In this situation the sum stops when the count changes

type (from -1 to 1, or 1 to -1) BUT the next count is EQUAL to the last one

the count continues. See example 4.

1 1

1 2

1 3

-1 -1

-1 -2

1 1

1 2

-1 -1

1 1

Example 4: I have included some of the relative movements that the 1’s and

-1’s are built off.

Raw data (count) I would want the number to add to each

other each time (sum cumulatively)

1.64500 1 1

1.64520 -1 -1

1.64520 -1 -2

1.64540 -1 -3 notice here that the two raw data values are EQUAL but we

still assign -1 and then add it in because it is part of the count. ONLY if

it were +1 would we stop the count.

1.64550 -1 -4

1.64550 1 1

1.64540 (changed to 40 just to show what I mean)

1.64570

1.64530

So what i would want the beginning bit of "Column B" to look like (I can’t

do it all manually)

Column A Column B

Raw data count sum count

1.64500 1 1

1.64520 -1 -1

1.64520 -1 -2

1.64540 1 1

1.64550 -1 -1

1.64550 1 1

1.64520 1 2

1.64570 1 3

1.64530 -1 -1

1.64510 -1 -2

1.64540 -1 -3

1.64550 1 1

1.64540 n/a n/a

1.64540 n/a n/a

1.64530 n/a n/a

Lastly,

If the count sums are negative, font = green, background = clear/normal

If the count sums are positive, font = red, background = clear/normal

If the count gets to + 9, cell = red, font = black bold

If the count gets to - 9, cell = green, font = black bold

basically i am totally stuck....i have tried writing some loops but mine

totally fail all the time....it's so so annoying. Sorry to ask for so much

help but i have been painfully staring at vba for dummies for the last week

and am stuck....

Thank you to anyone who can help me on this...

Jul 22, 2009, 5:43:01 PM7/22/09

to

Sam-

First, that was great of you to supply multiple examples. It was helpful in

trying to understand what you are looking to do. I think there is a simple

approach to get you what you want, although there are still a few missing

pieces.

I pasted your example data into column A. I then pasted this formula in B1:

IF(A1>=A2,1,IF(A1<A2,-1,0))

and copied the formula to the end of the data.

The only thing I'm not sure this fully addresses is your conflict between

whether a repeat value should end up as a +1 or -1, I didn't see a later

explanation on what your rules required.

Then I set C1 = 1, and in C2, I used this formula:

=IF(B2*C1>0,B2+C1,B2)

and copied it down.

Of course, you can combine all of this into one formula (all in column B) if

you wanted, but keeping it seperate makes it easier to troubleshoot.

I also didn't fully understand why your comparison was A2 vs A5; I used

sequential cells in these formulas, but you are welcome to edit as needed.

HTH,

Keith

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu