| View previous topic :: View next topic |
| Author |
Message |
gradstudent
Newbie
Joined: 21 Nov 2008
Posts: 3
|
 Posted: Fri Nov 21, 2008 11:16 am Post subject: countif with condition depending on multiple fields |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Nov 21, 2008 1:28 pm Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
gradstudent
Newbie
Joined: 21 Nov 2008
Posts: 3
|
| Posted: Mon Nov 24, 2008 6:09 am Post subject: |
|
|
| Villeroy wrote: | E2: =MATCH($D2;$A2:$C2;0) [copy down]
A5: =COUNTIF(D$2 $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 |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway
|
| Posted: Mon Nov 24, 2008 9:40 am Post subject: |
|
|
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 |
|
|
gradstudent
Newbie
Joined: 21 Nov 2008
Posts: 3
|
| Posted: Tue Nov 25, 2008 5:04 am Post subject: |
|
|
| 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 |
|
|
|
