kilograham
Level 1 (4 points)
iWork

Q: median if, Conditional medians

I want get the MEDIAN of a column IF it meets specific criteria found in another column.  EXCEL can do it with the following formula:

 

=MEDIAN(IF($A:$A="trade association",$B:$B))

 

"Because the formula itself references an array, you need to hit CTRL+SHIFT+ENTER instead of just ENTER when you've finished writing the formula."

 

That same formula didn't work in iWork Numbers.

 

Nor did this one

=IF($A:$A="trade association",MEDIAN($B:$B))

 

I know an easy solution would be to create a new column with an IF statement, but I'm trying to avoid that for a number of other reasons.

 

When I tried to import an Excel document into Numbers, I got the error "The array formula couldn't be imported and was replaced by the last calculated value."

 

TYPE

TOTAL REVENUE

trade association

$1,601,466,444

trade association

$732,697,005

professional society

$601,993,361

professional society

$586,676,226

trade association

$466,733,002

professional society

$462,700,048

trade association

$457,383,677

trade association

$389,161,833

trade association

$311,840,739

trade association

$276,005,771

Posted on Aug 25, 2016 10:47 AM

by kilograham,Solvedanswer
kilograham kilograham
Level 1 (4 points)
iWork
A:

Thanks for the definitive answer:  Numbers doesn't support "array formulas."  I can stop looking around now.

 

Numbers does support AVERAGEIF and SUMIF.  I hoped they'd support other functions too.

Posted on Aug 25, 2016 11:43 AM

Close
kilograham

Q: median if, Conditional medians

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Aug 25, 2016 11:09 AM in response to kilograham
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 11:09 AM in response to kilograham

    Numbers does not support so-called "array formulas" entered in Excel with ctrl-shift-enter.

     

    You can put the array in a range of cells (here an extra column), with something like this:

     

    Screen Shot 2016-08-25 at 2.04.05 PM.png

     

    The formula in column C is:

     

    =IF(A="trade association",B,"")

     

    Then take the median of that new column.

     

    In B2 of the second table I used:

     

    =MEDIAN(Data::C)

     

    SG

  • by kilograham,

    kilograham kilograham Aug 25, 2016 11:29 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Aug 25, 2016 11:29 AM in response to SGIII

    Thanks SG, but I'm trying to avoid creating a new table or columns with IF statements because I have to do this same formula for dozens of variables. 

     

    "I know an easy solution would be to create a new column with an IF statement, but I'm trying to avoid that for a number of other reasons."

  • by SGIII,

    SGIII SGIII Aug 25, 2016 11:36 AM in response to kilograham
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 11:36 AM in response to kilograham

    You don't have to put the MEDIAN formula in a new table; you could for example put it in a Footer Row at the bottom of  column C.

     

    Without some kind of a hack I'm pretty sure you will need a column for each variable, though. BTW, It's not a question of an "easy solution."  Numbers doesn't support Excel-style "array formulas."

     

    SG

  • by kilograham,Solvedanswer

    kilograham kilograham Aug 25, 2016 11:43 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Aug 25, 2016 11:43 AM in response to SGIII

    Thanks for the definitive answer:  Numbers doesn't support "array formulas."  I can stop looking around now.

     

    Numbers does support AVERAGEIF and SUMIF.  I hoped they'd support other functions too.

  • by SGIII,

    SGIII SGIII Aug 25, 2016 12:13 PM in response to kilograham
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 12:13 PM in response to kilograham

    kilograham wrote:

     

    Thanks for the definitive answer:  Numbers doesn't support "array formulas."  I can stop looking around now.

     

    Numbers does support AVERAGEIF and SUMIF.  I hoped they'd support other functions too.

     

    And UNION.RANGES and a few others that use arrays in memory.  But not Excel-style ctrl-shift-enter formulas.  In my experience (I'm a frequent Excel user too) the "workarounds," such as suggested above, are not as compact, but are generally quite practical.

     

    A list of Numbers formulas can be found here or via Help > Formulas and Functions Help in your menu.

     

    SG