Problem with Median IF formula

sharky12345

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,404
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

I'm trying to get the median from a range of values with 2 conditions - this formula is producing a #NUM error but I don't understand why if anyone can help?

"=MEDIAN(IF(DATA!$I$2:$I$2338=L$4,IF(DATA!$D$2:$D$2338=K5,DATA!$H$2:$H$2338)))"

For info, here's a snapshot of the data range;

Booking In TimeDayHour
334Sunday15-16
334Tuesday6-7
315Tuesday18-19
313Thursday18-19
301Tuesday15-16
285Tuesday16-17
255Thursday17-18
251Friday11-12
246Thursday22-23
229Tuesday11-12
226Thursday15-16
225Thursday19-20
224Thursday18-19
221Friday19-20
220Wednesday23-24
219Wednesday17-18
214Thursday22-23
213Friday11-12


In the formula, L4 = Monday and K5 = 0-1, (there are multiple examples in the data range that match that criteria).
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Your formula works for me.
It is an array formula that must be entered with CTRL-SHIFT-ENTER.
I changed Monday to Tuesday as there was no Monday in your example data.

Book1
HIJKL
1Booking In TimeDayHour
2334Sunday15-16Median
3334Tuesday0-1281.5
4315Tuesday18-19Tuesday
5313Thursday18-190-1
6301Tuesday15-16
7285Tuesday16-17
8255Thursday17-18
9251Friday0-1
10246Thursday22-23
11229Tuesday0-1
12226Thursday15-16
13225Thursday19-20
14224Thursday18-19
15221Friday19-20
16220Wednesday23-24
17219Wednesday17-18
18214Thursday22-23
19213Friday0-1
Sheet1
Cell Formulas
RangeFormula
J3,J19,J11,J9,K5J3=HOUR(B3)&"-"& HOUR(B3)+1
L3L3=MEDIAN(IF($I$2:$I$2338=$L$4,IF($J$2:$J$2338=$K$5,$H$2:$H$2338)))
 
Upvote 1
Solution
Thanks buddy!

However, there is sample data in the range that Matches Monday 0-1 - I have 19 records that meet that criteria so not sure what's going on.

I'll keep playing :)
 
Upvote 0
Further to the above, I now need to take it a step further and add another condition as below, but it's giving me the same result for each day of the week, (added as an array formula) - can anyone spot where I'm going wrong?

=IFERROR(MEDIAN(IF(DATA!$E$2:$E$527="TEAM A", DATA!$I$2:$I$527="Sunday",IF(DATA!$J$2:$J$527=$K4, DATA!$H$2:$H$527))),"NONE")
Click to expand...
 
Upvote 0
Try:

=IFERROR(MEDIAN(IF(DATA!$E$2:$E$527="TEAM A",IF(DATA!$I$2:$I$527="Sunday",IF(DATA!$J$2:$J$527=$K4,DATA!$H$2:$H$527,)))),"NONE")
 
Upvote 0
Nope, that still gives me the same result for every day of the week. I've done a manual check of the data and there are records there that match the criteria. I'll keep playing!

Thanks though
 
Upvote 0
How about posting a small sample and the results you expect.

Book1
EFGHIJKL
1TeamsBooking In TimeDayHour
2Team A334Sunday15-16Median
3Team B334Tuesday0-1301
4Team B315Tuesday18-1915-16Sunday
5Team B313Thursday18-19
6Team A301Sunday15-16
7Team B285Tuesday16-17
8Team A255Thursday17-18
9Team A251Friday0-1
10Team A246Thursday22-23
11Team B229Tuesday0-1
12Team A226Sunday15-16
13Team A225Thursday19-20
14Team B224Thursday18-19
15Team B221Friday19-20
16Team B220Sunday15-16
17Team B219Wednesday17-18
18Team B214Thursday22-23
19Team B213Friday0-1
DATA
Cell Formulas
RangeFormula
J3,J19,J11,J9J3=HOUR(B3)&"-"& HOUR(B3)+1
L3L3=IFERROR(MEDIAN(IF($E$2:$E$19="TEAM A",IF($I$2:$I$19="Sunday",IF($J$2:$J$19=$K4,$H$2:$H$19,)))),"NONE")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Sorry for the delay in replying. I've had to create a copy of the sheet and sanitise the data for the purposes of getting the sample;

TeamBlockTypeBooking TimeDayHour
TEAM CBLOCK 11334Sunday15-16
NOT FOUNDBLOCK 22334Tuesday6-7
TEAM DBLOCK 33315Tuesday18-19
TEAM ABLOCK 44313Thursday18-19
NOT FOUNDBLOCK 55301Tuesday15-16
TEAM DBLOCK 66285Tuesday16-17
TEAM EBLOCK 77255Thursday17-18
TEAM EBLOCK 88251Friday11-12
TEAM ABLOCK 99246Thursday22-23
NOT FOUNDBLOCK 1010229Tuesday11-12
TEAM EBLOCK 1111226Thursday15-16
TEAM ABLOCK 1212225Thursday19-20
TEAM EBLOCK 1313224Thursday18-19
TEAM EBLOCK 1414221Friday19-20
TEAM EBLOCK 1515220Wednesday23-24
TEAM DBLOCK 1616219Wednesday17-18
TEAM ABLOCK 1717214Thursday22-23
TEAM EBLOCK 1818213Friday11-12
TEAM EBLOCK 1919212Saturday21-22
TEAM ABLOCK 2020212Wednesday12-13
TEAM EBLOCK 2121210Thursday11-12
TEAM ABLOCK 2222206Wednesday20-21
TEAM ABLOCK 2323206Tuesday14-15
TEAM ABLOCK 2424206Wednesday19-20
TEAM DBLOCK 2525201Wednesday15-16
TEAM EBLOCK 2626201Wednesday11-12
TEAM ABLOCK 2727200Thursday22-23
TEAM CBLOCK 2828196Monday12-13
TEAM EBLOCK 2929196Wednesday15-16
NOT FOUNDBLOCK 3030196Monday7-8
TEAM CBLOCK 3131195Sunday23-24
TEAM CBLOCK 3232194Saturday23-24
TEAM EBLOCK 3333194Wednesday22-23
TEAM EBLOCK 3434190Wednesday14-15
TEAM EBLOCK 3535190Thursday22-23
TEAM BBLOCK 3636189Saturday23-24
TEAM EBLOCK 3737187Wednesday15-16
TEAM ABLOCK 3838185Thursday19-20
TEAM ABLOCK 3939184Tuesday12-13
TEAM CBLOCK 4040184Friday13-14
TEAM EBLOCK 4141183Saturday22-23
TEAM DBLOCK 4242181Friday12-13
TEAM ABLOCK 4343179Friday20-21
TEAM DBLOCK 4444178Saturday15-16
NOT FOUNDBLOCK 4545178Friday12-13
TEAM ABLOCK 4646178Wednesday11-12
TEAM DBLOCK 4747177Saturday12-13
TEAM DBLOCK 4848176Sunday5-6
TEAM BBLOCK 4949172Tuesday4-5
TEAM EBLOCK 5050172Thursday17-18
TEAM ABLOCK 5151171Wednesday19-20
TEAM ABLOCK 5252171Wednesday20-21
TEAM EBLOCK 5353170Friday20-21
TEAM EBLOCK 5454170Wednesday10-11
TEAM BBLOCK 5555169Friday22-23
TEAM EBLOCK 5656168Saturday22-23
TEAM EBLOCK 5757167Thursday13-14
TEAM ABLOCK 5858166Saturday18-19
TEAM CBLOCK 5959165Tuesday17-18
TEAM CBLOCK 6060165Saturday23-24
TEAM EBLOCK 6161164Wednesday22-23
TEAM EBLOCK 6262164Friday10-11
TEAM EBLOCK 6363164Saturday22-23
TEAM EBLOCK 6464164Thursday13-14
TEAM ABLOCK 6565162Wednesday21-22
TEAM DBLOCK 6666162Friday20-21
TEAM ABLOCK 6767160Tuesday18-19
TEAM EBLOCK 6868160Wednesday20-21
TEAM ABLOCK 6969159Tuesday18-19
TEAM DBLOCK 7070159Wednesday20-21
TEAM EBLOCK 7171159Thursday19-20
TEAM BBLOCK 7272158Tuesday20-21
TEAM ABLOCK 7373157Monday15-16
 
Upvote 0
Are these the results you would expect?

Book1
EFGHIJKLMNO
1TeamBlockTypeBooking TimeDayHourCriteriaCriteria
2TEAM CBLOCK 11334Sunday15-16TeamTEAM ATEAM E
3NOT FOUNDBLOCK 22334Tuesday6-7DayThursdayFriday
4TEAM DBLOCK 33315Tuesday18-19Hour19-2011-12
5TEAM ABLOCK 44313Thursday18-19Median205232
6NOT FOUNDBLOCK 55301Tuesday15-16
7TEAM DBLOCK 66285Tuesday16-17
8TEAM EBLOCK 77255Thursday17-18
9TEAM EBLOCK 88251Friday11-12
10TEAM ABLOCK 99246Thursday22-23
11NOT FOUNDBLOCK 1010229Tuesday11-12
12TEAM EBLOCK 1111226Thursday15-16
13TEAM ABLOCK 1212225Thursday19-20
14TEAM EBLOCK 1313224Thursday18-19
15TEAM EBLOCK 1414221Friday19-20
16TEAM EBLOCK 1515220Wednesday23-24
17TEAM DBLOCK 1616219Wednesday17-18
18TEAM ABLOCK 1717214Thursday22-23
19TEAM EBLOCK 1818213Friday11-12
20TEAM EBLOCK 1919212Saturday21-22
21TEAM ABLOCK 2020212Wednesday12-13
22TEAM EBLOCK 2121210Thursday12-13
23TEAM ABLOCK 2222206Wednesday20-21
24TEAM ABLOCK 2323206Tuesday14-15
25TEAM ABLOCK 2424206Wednesday19-20
26TEAM DBLOCK 2525201Wednesday15-16
27TEAM EBLOCK 2626201Wednesday11-12
28TEAM ABLOCK 2727200Thursday22-23
29TEAM CBLOCK 2828196Monday12-13
30TEAM EBLOCK 2929196Wednesday15-16
31NOT FOUNDBLOCK 3030196Monday7-8
32TEAM CBLOCK 3131195Sunday23-24
33TEAM CBLOCK 3232194Saturday23-24
34TEAM EBLOCK 3333194Wednesday22-23
35TEAM EBLOCK 3434190Wednesday14-15
36TEAM EBLOCK 3535190Thursday22-23
37TEAM BBLOCK 3636189Saturday23-24
38TEAM EBLOCK 3737187Wednesday15-16
39TEAM ABLOCK 3838185Thursday19-20
40TEAM ABLOCK 3939184Tuesday12-13
41TEAM CBLOCK 4040184Friday13-14
42TEAM EBLOCK 4141183Saturday22-23
43TEAM DBLOCK 4242181Friday12-13
44TEAM ABLOCK 4343179Friday20-21
45TEAM DBLOCK 4444178Saturday15-16
46NOT FOUNDBLOCK 4545178Friday12-13
47TEAM ABLOCK 4646178Wednesday11-12
48TEAM DBLOCK 4747177Saturday12-13
49TEAM DBLOCK 4848176Sunday5-6
50TEAM BBLOCK 4949172Tuesday4-5
51TEAM EBLOCK 5050172Thursday17-18
52TEAM ABLOCK 5151171Wednesday19-20
53TEAM ABLOCK 5252171Wednesday20-21
54TEAM EBLOCK 5353170Friday20-21
55TEAM EBLOCK 5454170Wednesday10-11
56TEAM BBLOCK 5555169Friday22-23
57TEAM EBLOCK 5656168Saturday22-23
58TEAM EBLOCK 5757167Thursday13-14
59TEAM ABLOCK 5858166Saturday18-19
60TEAM CBLOCK 5959165Tuesday17-18
61TEAM CBLOCK 6060165Saturday23-24
62TEAM EBLOCK 6161164Wednesday22-23
63TEAM EBLOCK 6262164Friday10-11
64TEAM EBLOCK 6363164Saturday22-23
65TEAM EBLOCK 6464164Thursday13-14
66TEAM ABLOCK 6565162Wednesday21-22
67TEAM DBLOCK 6666162Friday20-21
68TEAM ABLOCK 6767160Tuesday18-19
69TEAM EBLOCK 6868160Wednesday20-21
70TEAM ABLOCK 6969159Tuesday18-19
71TEAM DBLOCK 7070159Wednesday20-21
72TEAM EBLOCK 7171159Thursday19-20
73TEAM BBLOCK 7272158Tuesday20-21
74TEAM ABLOCK 7373157Monday15-16
Sheet1
Cell Formulas
RangeFormula
M5M5=MEDIAN(IF($E$2:$E$74=$M$2,IF($I$2:$I$74=$M$3,IF($J$2:$J$74=$M$4,$H$2:$H$74))))
O5O5=MEDIAN(IF($E$2:$E$74=$O$2,IF($I$2:$I$74=$O$3,IF($J$2:$J$74=$O$4,$H$2:$H$74))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Median / If (and Mode / If) not working
Replies
1
Views
111
Eric W
Eric W
N
  • Question
Take the Median of a Dataset with Multiple Criteria
Replies
6
Views
596
kevin9999
K
K
  • Question
Data entry form
Replies
5
Views
156
keranali
K
gtd526
  • Question
convert cell value to formula
Replies
8
Views
178
Fluff
Fluff
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
190
bh24524
B

Forum statistics

Threads
1,215,096
Messages
6,123,074
Members
449,093
Latest member
ripvw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top