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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]4/5/2017[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]4/7/2017[/TD]
[TD="align: right"]4/6/2017[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]5.5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]5/4/2017[/TD]
[TD="align: right"]4/7/2017[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]4/8/2017[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]5/1/2017[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]5/2/2017[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]5/3/2017[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]5/4/2017[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]5/5/2017[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]6/1/2017[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]6/2/2017[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]6/3/2017[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: right"]6/4/2017[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: right"]6/5/2017[/TD]

[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: right"]6/6/2017[/TD]

[TD="align: right"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: right"]6/7/2017[/TD]

[TD="align: right"]16[/TD]

</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