Solved

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

Posted on 2012-03-30
5
538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

617 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