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

countif with condition depending on multiple fields

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


Joined: 21 Nov 2008
Posts: 3

PostPosted: Fri Nov 21, 2008 11:16 am    Post subject: countif with condition depending on multiple fields Reply with quote

Hi,

I'm trying to write a spreadsheet formula to determine for each column how many of the columns values are maximum for that row. I've added a column that contains the maximum value for the row, but can't figure out how to write the countif condition that would compare a column to that column (rather than to a single value). To give an example, I have:

Code:

data1 data2 data3  max
 10    5     3     10
 12    5     8     12
 7    14     9     14


I would like to be able to get from this data the row (2, 1, 0), since data1 is maximal twice, data2 is maximal once, data3 0 times. The number of different columns in the spreadsheet is going to be changing, so I don't want to introduce a new column for each data column with a 1 if it is maximal and 0 if not and then count that. Is there any way to do this?

Thanks in advance.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 21, 2008 1:28 pm    Post subject: Reply with quote

E2: =MATCH($D2;$A2:$C2;0) [copy down]

A5: =COUNTIF(D$2:D$4;COLUMN(A1)) [copy to right]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
gradstudent
Newbie
Newbie


Joined: 21 Nov 2008
Posts: 3

PostPosted: Mon Nov 24, 2008 6:09 am    Post subject: Reply with quote

Villeroy wrote:
E2: =MATCH($D2;$A2:$C2;0) [copy down]

A5: =COUNTIF(D$2Very Happy$4;COLUMN(A1)) [copy to right]


Thanks for the reply, and this is fairly close to what I want. The only problem however is that multiple columns may contain the minimum, in which case i want to count all of them. With this formula the cell will be counted only if it contains the *first* minimum value in the row (because match only displays the first occurrence of the minimum). Is there anyway to work around this?

To give an example, If the input were:
Code:

data1 data2
10     5
8      8


I would want to get 2, 1, in the bottom row (column 1 is maximal in rows 1 and 2, column 2 is maximal in row 2).

Ideally what I would do is a pairwise comparison of each column to the max column and count the rows in which they are equal, but there doesn't seem to be any way to do this.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Nov 24, 2008 9:40 am    Post subject: Reply with quote

What about using =SUMPRODUCT(A$2:A$4=$D$2:$D$4) (Cell A5 in that first example)

(I know, it seems a little weird with a product from just one factor, but it works if I'm not terribly mistaken.)
Back to top
View user's profile Send private message
gradstudent
Newbie
Newbie


Joined: 21 Nov 2008
Posts: 3

PostPosted: Tue Nov 25, 2008 5:04 am    Post subject: Reply with quote

keme wrote:
What about using =SUMPRODUCT(A$2:A$4=$D$2:$D$4) (Cell A5 in that first example)

(I know, it seems a little weird with a product from just one factor, but it works if I'm not terribly mistaken.)


Thanks a lot, I got this to work after some playing around (I had some blank cells that weren't treated properly etc.) I assume this works because true evaluates to 1, false to zero, and the product of a single value is considered to be the value itself?

Cheers.
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