Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (risky or safe) and the partner assigned to them (P1 or P2).

Here is the an example of layout

  A           B          C            D
Client  |  Return  |  RiskCat  |  Partner
_________________________________________
John       1.5%       risky       P2
Bill       1.8%       risky       P1
Tim        1.2%       safe        P2
Bob        1.4%       risky       P1
Kate       2.1%       risky       P2
Fred       0.8%       safe        P1
...etc

I have written a "medianif()" function which calculates the median of returns in a given criteria (e.g. the risk category) So for example if I wanted to find the median of returns in the risky category I would do:

=median(if(C:C="risky",B:B,""))

And press ctrl-shift-enter to enter it as an array formula in the cell.

Likewise to find the median of returns for those who have P1 as the partner I would do:

=median(if(D:D="P1",B:B,""))

Both those formulae above work for me but say I wanted to find the median of returns for the risky category AND the P1 partner. Well, I try:

=median(if(and(C:C="risky",D:D="P1"),B:B,""))

Unfortunately this doesn't work and I get a #value error. Is is possible to use AND() in this way and if not how would I achieve what I've after?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

No, you can't use AND or OR in this way because those functions return a single result rather than an array. You need to use * instead of AND....or multiple IFs, e.g. either

=MEDIAN(IF((C:C="risky")*(D:D="P1"),B:B))

or

=MEDIAN(IF(C:C="risky",IF(D:D="P1",B:B)))

the latter is possibly marginally more efficient

share|improve this answer
    
Great, thanks, that works a treat. –  harryg Jan 11 '13 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.