Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fresh start/better explanation....totally stuck on loop code.pls h
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 -1s 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 1s€ (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 -1s€ (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/-1s (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 1s and -1s 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 cant 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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fresh start/better explanation....totally stuck on loop code.pls h
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*C10,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 "Sam" wrote: 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 -1s 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 1s€ (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 -1s€ (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/-1s (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 1s and -1s 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 cant 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totally stuck trying to format,loop etc...please help a novice!tha | Excel Programming | |||
Totally stuck trying to format,loop etc...re-post.please help | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming |