Solved

Posted on 2011-10-26

I'm trying to calculate medium based on two conditions. The full formula is

=IF(B4="All",MEDIAN(IF('Distinct Client'!$I$2:$I$2765="Homeless Prevention",'Distinct Client'!$O$2:$O$2765)),MEDIAN((('Distinct Client'!$I$2:$I$2765="Homeless Prevention")*('Distinct Client'!$AB$2:$AB$2765=B4)),'Distinct Client'!$O$2:$O$2765))

The area of formula I'm having trouble with is

MEDIAN((('Distinct Client'!$I$2:$I$2765="Homeless Prevention")*('Distinct Client'!$AB$2:$AB$2765=B4)),'Distinct Client'!$O$2:$O$2765))

=IF(B4="All",MEDIAN(IF('Di

The area of formula I'm having trouble with is

MEDIAN((('Distinct Client'!$I$2:$I$2765="Home

9 Comments

=MEDIAN((('Distinct Client'!$I$2:$I$2765="Home

('Distinct Client'!$I$2:$I$2765="Home

If not in an array formula, that will not return anything. Otherwise it will return an array of "true" or "false" for every cell. Then you multiply by ('Distinct Client'!$AB$2:$AB$2765=B4)

so if you shorten the range to, say, 5 cells, and in col I you have "Homeless Prevention" in the first 3 cells and "X" in the last two cells. Then in col AB you have "4,4,5,5,5", and B4 = 4

Then your formula evaluates as:

=Median({true,true,true,fa

=Median({1,1,0,0,0})

=0

is that how you mean it to work?

Try this

MEDIAN(IF(('Distinct Client'!$I$2:$I$2765="Home

regards, barry

barry

=MEDIAN(IF(('Distinct Client'!$I$2:$I$2765="Home

barry

=MEDIAN(IF(('Distinct Client'!$I$2:$I$2765="Home

barry

=MEDIAN(IF(('Distinct Client'!$I$2:$I$2765="Home

m4trix: Attached file is what I was trying to achieve.

Medium-Test.xlsx

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

How to delete a specific cell values in specific rows | 10 | 33 | |

Storing values to an array | 8 | 42 | |

Excel to create SQL insert | 7 | 28 | |

Check version | 13 | 38 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**6** Experts available now in Live!