# value error in Median formual

kvisaria

New Member
Joined
Jun 27, 2007
Messages
27
I am getting #value error when I try below formula. Can someone please advise

=MEDIAN(M7:M7382, IF($C$7:$C$7382,">="&DATE(2013,1,1),OR($C$7:$C$7382,"<="&DATE(2013,12,31))))
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
I am getting #value error when I try below formula. Can someone please advise

=MEDIAN(M7:M7382, IF($C$7:$C$7382,">="&DATE(2013,1,1),OR($C$7:$C$7382,"<="&DATE(2013,12,31))))

You formula looks like an array formula ,but maybe it should look like this?
=MEDIAN(M7:M7382, IF(OR($C$7:$C$7382,">="&DATE(2013,1,1),$C$7:$C$7382,"<="&DATE(2013,12,31)),M7:M7382))

Confirm With Ctrl+Shift+Enter, not just Enter
 

kvisaria

New Member
Joined
Jun 27, 2007
Messages
27
You formula looks like an array formula ,but maybe it should look like this?
=MEDIAN(M7:M7382, IF(OR($C$7:$C$7382,">="&DATE(2013,1,1),$C$7:$C$7382,"<="&DATE(2013,12,31)),M7:M7382))

Confirm With Ctrl+Shift+Enter, not just Enter

I am getting #value error, do you know why?
 

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254

ADVERTISEMENT

Hi

I was a bit to quick there With my answer ;). Lot of errors in my formula. Try this one:
=MEDIAN(IF(OR($C$7:$C$7382>=DATE(2013,1,1),$C$7:$C$7382<=DATE(2013,12,31)),$M$7:$M$7382))
And remember to confirm With Ctrl+Shift+Enter.
 

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254

ADVERTISEMENT

You're welcome. Glad it worked out :)
 

kvisaria

New Member
Joined
Jun 27, 2007
Messages
27
I am sorry, it works but didn't realize that its including all the dates in between so its including all the dates that are in 2014 also. Not sure why
Name Date Rate
A 5-Apr-12 12
B 9-Apr-12 13
C 9-Apr-12 14
D 11-Apr-12 15
E 13-Apr-12 16
F 14-Jan-14 20
G 15-Jan-14 21
H 16-Jan-14 22
I 13-Apr-12 17
J 13-Apr-12 18

MEDIAN(IF(OR(B2:B11>=DATE(2012,4,5),B2:B11<=DATE(2012,4,13)),C2:C11))
 

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
You're right
I'm Learning something New every day. The OR function can't be used for this purpose. Try this instead:
=MEDIAN(IF(B2:B11>=DATE(2012,4,5),IF(B2:B11<=DATE(2012,4,13),C2:C11)))
And again, remember to confirm With Ctrl+Shift+Enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top