[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Calculating Medium using two Criteria

Posted on 2011-10-26
Medium Priority
187 Views
Last Modified: 2012-05-12
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))

0
Question by:jbakestull
• 4
• 3
• 2
9 Comments

LVL 7

Expert Comment

ID: 37031857
you have ranges in your "IF" criteria - are you sure you are entering it as an array formula? (CTRL + SHIFT + Enter)
0

Author Comment

ID: 37031905
I did (CTRL + SHIFT + Enter), but there are no if's in formula statement.

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

LVL 7

Expert Comment

ID: 37032032
would you be able to provide a sample workbook? I'm confused by what you're looking for. within the median formula you have:
('Distinct Client'!\$I\$2:\$I\$2765="Homeless Prevention")
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), which will also not work unless it's an array formula. If so it will return an array of "true" or "false" for every cell that matches B4. Then both are multiplied together, so you get a 1 every time both cells are true, and 0 every other time. Then you're taking a median of that, meaning it will only ever come out to 1 or 0.  Perhaps that's your goal.

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,false,false} * {true,true,false,false,false})
=Median({1,1,0,0,0})
=0

is that how you mean it to work?

0

LVL 50

Expert Comment

ID: 37032125
You need an IF

Try this

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

regards, barry
0

LVL 50

Expert Comment

ID: 37032171
Note: the part I quoted forms the FALSE part of your initial IF function, so would be followed by one more closing parenthesis ( closing the IF function)

barry
0

LVL 50

Expert Comment

ID: 37032235
...you could even use this version as the whole formula....incorporating the possibility of "All" in B4....

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

barry
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 37032548
Sorry, typo crept into that last one - should be

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

barry
0

Author Comment

ID: 37032557
thanks barryhoudini,

=MEDIAN(IF(('Distinct Client'!\$I\$2:\$I\$2765="Homeless Prevention")*('Distinct Client'!\$AB\$2:\$AB\$2765=IF(B4="All",'Distinct Client'!\$AN\$2:\$AB\$2765,B4)),'Distinct Client'!\$O\$2:\$O\$2765)) was correct formula.

m4trix: Attached file is what I was trying to achieve.
Medium-Test.xlsx
0

Author Closing Comment

ID: 37032559
Thank You.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst youāll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dā¦
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overā¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month19 days, 9 hours left to enroll

#### 872 members asked questions and received personalized solutions in the past 7 days.

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