Solved

formula in excel for including only neg/neg and positive positive values in separate columns

Posted on 2012-03-30
5
533 Views
Last Modified: 2012-07-11
Ok. I have an excel file with data. I want to control for an inverse relationship between the two columns so I want to create a separate set of columns that include only values with either negative values in both columns or positive values in both columns. There will be more rows than listed below and additional columns as seen in the attached file. For example, I have the data below: If this can't be done in excel, I how would it be done in perl when looking at an excel file? The output file could simply contain the final three columns of data.

column A    column M column O
1/1/80        -1.30        2.22
2/1/80        .34           .43
3/1/80        .55           -.57
4/1/80        -.22          -.90

My update excel file would contain this

column A    column M column O column Q column R column S
1/1/80        -1.30        2.22          2/1/80        .34           .43
2/1/80        .34           .43            4/1/80        -.22          -.90
3/1/80        .55           -.57
4/1/80        -.22          -.90
Mastersdata.xls
0
Comment
Question by:libertyforall2
5 Comments
 
LVL 2

Assisted Solution

by:alpha456
alpha456 earned 125 total points
ID: 37790723
This can be done in Excel, almost for sure, but it slightly depends on what you want at the end. So...
1.  You could add a column that multiplies columns M and O. In that new column, any positive number is a result of both M and O being positive or negative (or use an IF statement or an AND statement to achieve the same thing), so then you can set a filter on all your data and filter out all negative numbers, leaving your spreadsheet just showing the rows you are interested in. You could then manually copy the data to another sheet.

2. You could create columns Q, R and S as you show them in your question using formulae with lookup formulae, but the formulae would have to go all the way down to row 385, whereas the information you want to show would stop before then, so you would have to have zeroes or something in the cells below that point. Not clear if this matters to you.

3. Otherwise, you could use a macro to run down your data, select the rows that meet the criteria and copy the relevant information to the cells as you lay it out in your question.
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 250 total points
ID: 37791258
See if this is what you're looking for. In columns AH - AJ I have formulas that select the matching data. The macro copies that data to columns Q-S, eliminating the blank rows.

Flyster
Mastersdata-Sample.xls
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 125 total points
ID: 37792364
Here is one method which uses a helper column and uses match, index and offset functions. I have used random numbers and you can press F9 to see it change.
Extrposneg.xlsx
0
 
LVL 22

Accepted Solution

by:
Flyster earned 250 total points
ID: 37793453
To ssaqibh,

That's sweet!

Paul
0
 

Author Closing Comment

by:libertyforall2
ID: 38177042
Great
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Apps blocked by Java 9 64
Formula or Macro to determine variance 17 75
Excel 2016 formulas 5 30
Excel vba to add signature to email when created 11 39
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now