OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Find rows based upon TWO other columns, without Data Filter

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
NOYGDB
General User
General User


Joined: 02 Oct 2007
Posts: 44

PostPosted: Wed Jul 02, 2008 10:51 pm    Post subject: Find rows based upon TWO other columns, without Data Filter Reply with quote

Ok, here's one that's got me stumped:

In Column B, I've got percentage numbers. In Column F, I've got several pre-designated text blurbs.

I'd like to count the number of rows that are positive or negative in Column B, but only if there is a specific text blurb in Column F.

For instance, here's the first line of the spreadsheet:
Symbol: % Chg: Net Chg: Counter: Industry: Sector:
AA -6.36% -2.19 5 Metal Mining Basic Materials

So, since Column B is negative, and Column F is "Basic Materials", I'd increment the 'negative' count for Basic Materials by one.

This will allow me to keep track of the number of stocks that are positive or negative for the day, in each Sector or Industry.

Any ideas?
Back to top
View user's profile Send private message
machinegun
General User
General User


Joined: 24 Mar 2005
Posts: 7

PostPosted: Thu Jul 03, 2008 4:59 am    Post subject: Reply with quote

can supply a few more rows of data that match/do not match your criteria?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Jul 03, 2008 5:09 am    Post subject: Re: Find rows based upon TWO other columns, without Data Fil Reply with quote

NOYGDB wrote:
Ok, here's one that's got me stumped:

In Column B, I've got percentage numbers. In Column F, I've got several pre-designated text blurbs.

I'd like to count the number of rows that are positive or negative in Column B, but only if there is a specific text blurb in Column F.

For instance, here's the first line of the spreadsheet:
Symbol: % Chg: Net Chg: Counter: Industry: Sector:
AA -6.36% -2.19 5 Metal Mining Basic Materials

So, since Column B is negative, and Column F is "Basic Materials", I'd increment the 'negative' count for Basic Materials by one.

This will allow me to keep track of the number of stocks that are positive or negative for the day, in each Sector or Industry.

Any ideas?


I second machinegun's request. Also can you explain where Counter is coming from. I don't understand why you would be incrementing Counter. Is this a running total of the number of + and - increments for this particular stock? If so does that mean that you are replacing the values in column B everyday?
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Jul 03, 2008 5:27 am    Post subject: Reply with quote

You might use some [hidden] helper column[s], say column H. Let G1 contain the text in column F being searched. You can expand on this as necessary [last comment below].

I don't have time at the moment to outline everything, but you'd have something like =IF(AND(B1<0;F1=G1);1;"") so indicating a "1" when the negative condition is met. Do another for the positive. Hide columns not needed for report.

A counter cell, say M1, can keep track of the number of incidences by summing this hidden column of negative condition with =SUM(H1:H100) or something like that.

If necessary, you can list the sector criteria in one column with the summed values in an adjacent column. ...

David.
Back to top
View user's profile Send private message
NOYGDB
General User
General User


Joined: 02 Oct 2007
Posts: 44

PostPosted: Thu Jul 03, 2008 3:59 pm    Post subject: Reply with quote

All the columns are cut-and-pasted from TradeStation... the counter is a quick-reference way of telling if a stock meets all the parameters that I'm looking for. It can be ignored... it was just easier to include it in the spreadsheet, so I could do a quick cut-and-paste from TradeStation to the spreadsheet, without jiggering with columns and layout each time.

Ok, here's the first few lines:
Symbol: % Chg: Net Chg: Counter: Industry: Sector
AA -6.36% -2.19 5 Metal Mining Basic Materials
AAU 2.40% 0.04 4 Gold & Silver Basic Materials
AAII -17.69% -0.32 3 Aerospace & Defense Capital Goods
ABB -3.79% -1.06 5 Conglomerates Conglomerates
ABAT -6.88% -0.4 4 Appliance & Tool Consumer Cyclical
AAV -2.82% -0.36 3 Oil & Gas Operations Energy
AAME 3.12% 0.05 4 Insurance (Life) Financial

So, as you can see, in Columns E and F, there can be any of several different (but known) text blurbs.

What I'd like to do, for instance, is to have two counters... one for stock symbols in a given Sector or Industry that are positive on the day, and one for those that are negative.

Thus, for the Sector of "Basic Materials", it would see stock symbol AA is down for the day, and would increment the "down stocks' counter by one. It would also see that stock symbol AAU is up for the day, and would increment the 'up stocks' counter by one... but ONLY for the stocks that are in the 'Basic Materials' Sector.

I would use different cells to get the price up / price down data for each of the Sectors or Industries.

Thus, I can compare up : down stocks in any given Sector or Industry quickly.

It would be something like this:
=COUNTIF(AND(E3:E10000;"Chemical Manufacturing";B3:B10000;">0"))
or
=COUNTIF(AND(F3:F10000;"Basic Materials";B3:B10000;">0"))

But that doesn't work.
Back to top
View user's profile Send private message
NOYGDB
General User
General User


Joined: 02 Oct 2007
Posts: 44

PostPosted: Thu Jul 03, 2008 5:27 pm    Post subject: Reply with quote

Oh, wait... I think I can use DCOUNTA. Give me a bit to experiment.

{EDIT}
Eh... no. You need a label row above each data row for DCOUNTA... meaning I'd have to have alternating data and label rows... I could do that, and hide the label rows, I guess, but it's such a kludge.

Any other ideas?
Back to top
View user's profile Send private message
NOYGDB
General User
General User


Joined: 02 Oct 2007
Posts: 44

PostPosted: Thu Jul 03, 2008 9:04 pm    Post subject: Reply with quote

Ok, so I went with the DCOUNTA idea above... and it works, although it IS a kludge.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group