• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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

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
libertyforall2
Asked:
libertyforall2
4 Solutions
 
alpha456Commented:
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
 
FlysterCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
FlysterCommented:
To ssaqibh,

That's sweet!

Paul
0
 
libertyforall2Author Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now