excelworksheet-functiongnumeric

How can I perform functions on a specified subset of rows?

I want to find the max (min, average, etc.) of a column but only for the subset of rows where another column matches a certain pattern.

For example, here is the data in the sheet named "data":

Date   Value
Jan    15
Jan    17
Jan    3
Feb    19
Feb    34
Feb    37

Then in a separate sheet, "reports", I'd like to have:

       Jan    Feb
Max    17     37
Min    3      19

What function I can put in the cells in the "reports" sheet to get those values?

If it matters, I'm using Gnumeric 1.10.

Solution

  • To achieve:

    =max(column b where column a == Jan)

    You can use an IF to do that:

    =MAX(IF(A2:A7="Jan", B2:B7))

    This formula should be entered as an array formula however. In excel, you do this with Ctrl+Shift+Enter.