Excel pro help needed (1 Viewer)

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Hi,

I am currently working on some excel workbooks and am struggling with how to proceed further. I have data that come in to excel, data that is in decimals and also in +ve and -ve format. This data is constantly changing. What i would like to do is select the column where the data input changes value from nagative to positive, find the row/s where the changes in value have happened, then go to a seperate column in the sheet but the same row and extract the value that lies in the other column and same row.

How can this be done ? Any online help or online tutor who can show me via web or conference facility would be grea.

Many Thanks

Razor
 

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
I have attached an image to clarify things further.

I am looking to find a specified value in the yellow column, in this case where the value changes from -ve to +ve, as shown by the orange cells. Then i would like to go to another column, same row and extract that value as shown by the green cells.

Thanks
 

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
cant upload image, too big and cant reduce it
 

shades

Registered User.
Local time
Today, 14:18
Joined
Mar 25, 2002
Messages
516
Howdy, and welcome. Can you provide a small sample of the workbook and then zip it?
________
VAPORIZERS
 
Last edited:

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Thanks shades, have attavhed a workbook. Any help would be greatly appreciated.

Many Thanks
 

Attachments

  • excel11.zip
    21.2 KB · Views: 159

shades

Registered User.
Local time
Today, 14:18
Joined
Mar 25, 2002
Messages
516
Howdy. So these values in column D automatically change when the data changes? How are you wanting to determine that change? By formula? A different worksheet? How often are the values changed? You reference by formula to the worksheet market_markups_02_03_07.xls. In other words, how can Excel determine that you want rows 7, 22, and 51 highlighted based on the value in column D?

Once that criteria (on determining change) is set, then finding the corresponding value in column H can be done.
________
Og Kush Marijuana Strain
 
Last edited:

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Shades. Thank you for your reply.
So these values in column D automatically change when the data changes? How are you wanting to determine that change? By formula? A different worksheet? How often are the values changed?

The values change in all the columns continually. The change should be determined in column H when the value changes from negative to positive, the cell where this occurs , H7,H22 and H51 , should be singled out. Then the sheet should go along that row, eg H7 and find the value in the corresponding column D7. This value in D& is the value that should be extracted and placed other cells on a worksheet for reference purposes later.

how can Excel determine that you want rows 7, 22, and 51 highlighted based on the value in column D?


Excel should etermin that i wantrows 7,22 and 51 because of the change in value from negative to positive in column H.


You reference by formula to the worksheet market_markups_02_03_07.xls

This sheet is updated from another sheet under that name.



Thank you
 

shades

Registered User.
Local time
Today, 14:18
Joined
Mar 25, 2002
Messages
516
how can Excel determine that you want rows 7, 22, and 51 highlighted based on the value in column D?


Excel should etermin that i wantrows 7,22 and 51 because of the change in value from negative to positive in column H.

As old as I am, I need all the help I can get to understand. ;)

My question remains, how can Excel determine that there is a change in H7, H22, H51? Is the original value supposed to be stored somewhere to make that comparison? If so, how can you determine when that will be? It seems as if the "original value" needs to be stored and then when an update comes compare, and then use the new update as "original" for the next.

But i may still be misunderstanding.
________
Europe
 
Last edited:

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Helo Shades,

Sorry for not being clear. These values in H get updated quite regularly. There are only 150 rows but the last one falls off at specified intervals as row 2 updates with new data, the other 49 are stored for comparison purposes. if there has been a flow of positive numbers in row 2 and the next number is a negative, this is when excel should determine there has been a change, this number should then be stored to another sheet for later reference.

So really the answer to when would be as the new data gets updated in row 2 and is compared to the previous data in row 3. I hope this helps. Thank you for taking the time.
 

Squidinker

Registered User.
Local time
Today, 19:18
Joined
Jun 28, 2007
Messages
11
Hey Razor,

I'm not 100% sure I have absolutely, completely and unequivocally understood what you need but (and with those caveats over with), please have a look at the attached xls (it was made in 2k7 btw so I hope the functionality is still in there).

As I understand, you need to save values in a given row where the column H value changes from positive to negative. At least I hope that's it.

The attached xls does that in a way. Not a clean, elegant or useful way... but it's there anyway.

It requires two new columns at the right of the data sheet (1). The first, "Sign" determines whether the column H value is positive or negative and assigns a "+1" or "-1" accordingly.
The second new column, "Highlight?" determines whether column H turns from positive to negative in the following minute sampled. It does this simple by subtracting the proceeding column H value from the current H value. A value of 2 for this can only be given if the proceeding value is negative and its preceding value is positive.
If this value is met, the word "Look!" is printed, otherwise it stays blank.

I then created a second spreadsheet which is designed to pull up the values in columns D and H when the condition is met. It is also a simple IF formula that only prints the D and H values for a given row if the "Highlight?" column says "Look!".

You will note that they are not listed at the top of the second sheet but in their relative positions separated by a lot of supposedly empty cells. These cells contain the same formula and have to since I don't know how create a query which would list data sequentially according to criteria in Excel. As far as I know (and my knowledge is limited) that is certainly the domain of a database.

Also, you need to add the formulae for the two new columns for each new line (minute) of data.

As I said, not elegant and probably not useful.

Still, I hope helps in some way.


Squid


Ps. I should add that you can do away with the "Highlight?" column in the first sheet and put the checking function into the formulae on sheet 2 by using this formula:

=IFERROR(IF((Sheet1!K2-Sheet1!K1)=2,Sheet1!D2,""),"")
This prints column D. For H, just change D2 to H2 then propagate it down.
 

Attachments

  • excel1Squid.zip
    33.8 KB · Views: 153
Last edited:

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Hello Squidinker.

Thank you so much for the excel spreadsheet. I will have a play about with them formulas and updates and see what happens but you are certainly on the right track. Will this pull out any new changes in value on the second row?

Thank you very much I am very grateful.
 

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Hello squidinker. I have tested it on my worksheet. The first column(sign) seems to work ok, but the second column comes up with a #NAME error. I am using excel 2003 and have installd this function in the VBA module so it now appears in the formulas list, but I still seem to be getting the error.


Thank you
 

Squidinker

Registered User.
Local time
Today, 19:18
Joined
Jun 28, 2007
Messages
11
The error is probably coming from my just cutting and pasting the formula from my xls - it is likely looking for a cell in a sheet that is not there.

The second column "Highlight?" is simply there to break the process down. It doesn't bring up a value but highlights if it is one that you are looking for (where the "MACDHist(12,26,9,C)" column goes from a positive value to a negative).
If it comes up with a positive ("Look!") then that Look! value can be used in an if statement to select values elsewhere in the row which is what I did on the second sheet.

The formula I copied at the bottom of the last post was simple a combination of the Sheet2 formulae and the highlight formula - i.e. you can do away with the "Highlight?" column.
Here are it's parts:

=IFERROR(IF((Sheet1!K2-Sheet1!K1)=2,Sheet1!D2,""),"")

IFERROR is in there to make the sheet cleaner. If the rest of the formula comes up with an error, it allows you to set another value (other than the generic excel error message). I have used two parenthesis at the end with nothing between so the cell looks empty.

Removing the IFERROR;
IF((Sheet1!K2-Sheet1!K1)=2,Sheet1!D2,"")

And since I copied from the sheet, we can remove the sheet references;
IF((K2-K1)=2,D2,"")

The question in the IF statement, (K2-K1)=2 asks whether the proceeding cell value is negative and the preceding cell value is positive (which would give the value 2) which is the scenario you are looking for. It does this by looking up the values in the "Sign" column (K).
IF it does equal 2 (proceeding cell negative & preceding cell positive), then the cell output is set as D2. It can be any cell you want, I chose it because you highlighted it in green (column D).
To fetch the associated value from another column you would use the same formula and change D2 to H2 for example.

I'm not entirely sure how you would fit this into VB though. You still need two separate cells, one for the "Sign" and another for the IF statements.
 
Last edited:

Squidinker

Registered User.
Local time
Today, 19:18
Joined
Jun 28, 2007
Messages
11
Argh, please disregard.

=IF(AND((H2>0),(H1<0))=TRUE,D2,"")

^^ much better.

Single column, much cleaner.

It simply says what we want; IF the proceeding cell is negative AND the preceding cell is positive (then) show cell D2 (or whatever cell you want) (otherwise) print nothing.
 

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Thanks Squidinker. That now works perfectly. Thats really superb.
If you are ever around Bluewater, treats on me.

Shades, Thank you also for your help. Same applies to you, if you are around Bluewater, my shout.


Thank you so much.
 

razor

Registered User.
Local time
Today, 12:18
Joined
Jun 27, 2007
Messages
10
Sorry squidinker, how can i import the data to a new sheet from the last formula?

Thank you
 

Squidinker

Registered User.
Local time
Today, 19:18
Joined
Jun 28, 2007
Messages
11
Sorry squidinker, how can i import the data to a new sheet from the last formula?

Thank you


Hmm, that is the question... you can put the formula in another sheet easily - you just have to make sure that you reference the cells in the specified sheet properly;

instead of K2 you need Sheet1!K2 where Sheet1! is the name of the sheet with a "!" at the end to denote the reference.

However you will need one formula on your new sheet for each row of raw data on your original sheet. Ideally, we would have a succinct list of values which pass the selection test however as far as I know, standard excel can't do this - you will not only have large blank areas where the values on the raw data page don't meet the selection punctuated by the odd row which does, BUT you will also need to add a new line of formula for each new line of raw data.

As I say, I don't know of a way to automatically pull the results into a nice neat list though I suppose VB people would be able to.

Ironic because the issues I am having in part stem from this problem and have led me to using access which would be able to automatically list your values without a seconds thought.

Adding a new line with the formula should be simple enough though with a macro.
 

shades

Registered User.
Local time
Today, 14:18
Joined
Mar 25, 2002
Messages
516
If I understand correctly, you could use VBA to copy, PasteSpecial Values to another worksheet, then delete all rows with value 0 or "".
________
Weed vaporizers
 
Last edited:

Users who are viewing this thread

Top Bottom