Median, Mode, Maxim, & minimum with If & multiple criteria

[mmunoz20]

Hi.

I have a question regarding how to find the median, mode, maximum, minimum & maximum with multiple criteria. I understand that I have to use the if function, but I am not sure how to go about it. I attempted to find the median, but I got an error (#value!). Here is the formula that I wrote:

=MEDIAN(IF(Database!$C$2:$C$10000,">="&B3, Database!$C$2:$C$10000"<="&C3), Database!$N$2:$N$10000)

Context: I am trying to find the median for a set of numbers that fall in a particular date range (>= B3 & <=C3).

Any suggestions are welcomed & appreciated!!

Thanks,
Maria

 

[AhoyNC]

re: Median, Mode, Maxim, & minimum with If & multiple criteria

This would be an array formula that must be entered with CTRL-SHIFT-ENTER (not just ENTER).
Also, syntax should be something like below.

Excel Workbook
	A	B	C	D	E	F	N
1
2			4/5/2017				1
3		4/7/2017	4/6/2017		5.5		2
4		5/4/2017	4/7/2017				3
5			4/8/2017				4
6			5/1/2017				5
7			5/2/2017				6
8			5/3/2017				7
9			5/4/2017				8
10			5/5/2017				9
11			6/1/2017				10
12			6/2/2017				11
13			6/3/2017				12
14			6/4/2017				13
15			6/5/2017				14
16			6/6/2017				15
17			6/7/2017				16
Sheet

 

[mmunoz20]

re: Median, Mode, Maxim, & minimum with If & multiple criteria

Thank you!

This would be an array formula that must be entered with CTRL-SHIFT-ENTER (not just ENTER).
Also, syntax should be something like below.

	A	B	C	D	E	F	N
1
2			4/5/2017				1
3		4/7/2017	4/6/2017		5.5		2
4		5/4/2017	4/7/2017				3
5			4/8/2017				4
6			5/1/2017				5
7			5/2/2017				6
8			5/3/2017				7
9			5/4/2017				8
10			5/5/2017				9
11			6/1/2017				10
12			6/2/2017				11
13			6/3/2017				12
14			6/4/2017				13
15			6/5/2017				14
16			6/6/2017				15
17			6/7/2017				16

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:35px;"><col style="width:64px;"><col style="width:25px;"><col style="width:53px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 {=MEDIAN(IF($C$2:$C$17>B3,IF($C$2:$C$17<b4,$n$2:$n$17)))}< td=""></b4,$n$2:$n$17)))}<> <tbody> </tbody>

Formula Array: Produce enclosing{ } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4