Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: # value error in Median formual

  1. #1
    New Member
    Join Date
    Jun 2007
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default # value error in Median formual

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

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,148
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    .
    .

    Your IF function doesn't look right. It should have this syntax:

    IF(logical_test, [value_if_true], [value_if_false])

    IF function - Excel

  3. #3
    Board Regular Vidar's Avatar
    Join Date
    Jul 2012
    Location
    Norway
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    Quote Originally Posted by kvisaria View Post
    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
    Earlier I was very much in doubt, now I am not so sure anymore...

  4. #4
    New Member
    Join Date
    Jun 2007
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    Quote Originally Posted by Vidar View Post
    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?

  5. #5
    Board Regular Vidar's Avatar
    Join Date
    Jul 2012
    Location
    Norway
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    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.
    Earlier I was very much in doubt, now I am not so sure anymore...

  6. #6
    New Member
    Join Date
    Jun 2007
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    It worked. Thanks so much for your help!!

  7. #7
    Board Regular Vidar's Avatar
    Join Date
    Jul 2012
    Location
    Norway
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    You're welcome. Glad it worked out
    Earlier I was very much in doubt, now I am not so sure anymore...

  8. #8
    New Member
    Join Date
    Jun 2007
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

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

  9. #9
    Board Regular Vidar's Avatar
    Join Date
    Jul 2012
    Location
    Norway
    Posts
    1,254
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    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.
    Earlier I was very much in doubt, now I am not so sure anymore...

  10. #10
    New Member
    Join Date
    Jun 2007
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: # value error in Median formual

    It works. thanks so much for your help!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •