OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (
safe) and the partner assigned to them (
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:
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:
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:
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?