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

x
?
Solved

Calculating Medium using two Criteria

Posted on 2011-10-26
9
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
Comment
Question by:jbakestull
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

by:m4trix
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

by:jbakestull
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

by:m4trix
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

Expert Comment

by:barry houdini
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

by:barry houdini
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

by:barry houdini
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

by:
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

by:jbakestull
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

by:jbakestull
ID: 37032559
Thank You.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

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.

Join & Ask a Question