Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-27-2007, 04:20 AM   #1
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
Excel pro help needed

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 is offline   Reply With Quote
Old 06-27-2007, 04:47 AM   #2
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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 is offline   Reply With Quote
Old 06-27-2007, 05:06 AM   #3
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
cant upload image, too big and cant reduce it

razor is offline   Reply With Quote
Old 06-27-2007, 06:10 AM   #4
shades
Registered User
 
Join Date: Mar 2002
Location: Fly-over territory
Posts: 516
Thanks: 0
Thanked 1 Time in 1 Post
shades is on a distinguished road
Howdy, and welcome. Can you provide a small sample of the workbook and then zip it?
________
VAPORIZERS

Last edited by shades; 02-16-2011 at 01:19 AM.
shades is offline   Reply With Quote
Old 06-27-2007, 06:30 AM   #5
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
Thanks shades, have attavhed a workbook. Any help would be greatly appreciated.

Many Thanks
Attached Files
File Type: zip excel11.zip (21.2 KB, 75 views)
razor is offline   Reply With Quote
Old 06-27-2007, 07:19 AM   #6
shades
Registered User
 
Join Date: Mar 2002
Location: Fly-over territory
Posts: 516
Thanks: 0
Thanked 1 Time in 1 Post
shades is on a distinguished road
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 by shades; 02-16-2011 at 01:19 AM.
shades is offline   Reply With Quote
Old 06-27-2007, 09:59 AM   #7
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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

razor is offline   Reply With Quote
Old 06-27-2007, 01:53 PM   #8
shades
Registered User
 
Join Date: Mar 2002
Location: Fly-over territory
Posts: 516
Thanks: 0
Thanked 1 Time in 1 Post
shades is on a distinguished road
Quote:
Originally Posted by razor View Post
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 by shades; 02-16-2011 at 01:19 AM.
shades is offline   Reply With Quote
Old 06-27-2007, 10:34 PM   #9
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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.
razor is offline   Reply With Quote
Old 06-28-2007, 09:20 AM   #10
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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.
Attached Files
File Type: zip excel1Squid.zip (33.8 KB, 65 views)

Last edited by Squidinker; 06-28-2007 at 09:30 AM.
Squidinker is offline   Reply With Quote
Old 06-28-2007, 02:16 PM   #11
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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 is offline   Reply With Quote
Old 06-28-2007, 02:54 PM   #12
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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
razor is offline   Reply With Quote
Old 06-28-2007, 04:00 PM   #13
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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 by Squidinker; 06-28-2007 at 04:07 PM.
Squidinker is offline   Reply With Quote
Old 06-28-2007, 04:03 PM   #14
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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.
Squidinker is offline   Reply With Quote
Old 06-28-2007, 10:21 PM   #15
razor
Registered User
 
Join Date: Jun 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
razor is on a distinguished road
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 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending an Excel file to an Access table adrian.stock22 General 7 12-12-2008 02:47 PM
Query to fill in blanks within a table? Maybe some other way? robjones23 Queries 11 03-21-2005 03:10 AM
Excel automation, cannot quit Excel instance antifashionpimp Modules & VBA 5 11-22-2004 04:44 AM
access to excel automation...seem to still have a hidden excel instance.... mytfein Modules & VBA 2 11-03-2004 08:27 AM
[SOLVED] The not. excel db? mission2java_78 General 4 01-29-2003 11:38 AM




All times are GMT -8. The time now is 04:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World