+ Reply to Thread
Results 1 to 9 of 9

median shows wrong result when using formula as a cell value

  1. 06-09-2015, 08:33 AM #1
    thucydides
    thucydides is offline
    Registered User
    Join Date
    06-09-2015
    Location
    Croatia
    MS-Off Ver
    2010
    Posts
    8

    median shows wrong result when using formula as a cell value

    So, i know that the title is a little incomprehensible... I'm trying to make some kind of a alerting system for data coming from rs232 (from digial scale). Those values have to be within limits that are already specified.That part was not hard to figure out, but im having trouble with formulas.

    Data from scale is automatically captured to A5.
    In B5 i have formula to eliminate extra symbols from captured values:
    =(MID(A5;3;3))
    C5 and D5 contains upper and lower values.

    Formula in question is (some random cell, lets say F5, im using merged cells for it):
    =IF(ISBLANK(B5);"ERR";IF(B5=MEDIAN(C5:D5);"Yes";"No"))

    and im getting wrong results from it. Eg. when formula in B5 is manually replaced with number that is within limits of C5 and D5, result of formula in F5 is correct, when using formula (=(MID(A5;3;3))) that same value, when coming from RS232 port, it shows wrong result.

    I have attached xls so you can see what im talking about
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-09-2015, 08:41 AM #2
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: median shows wrong result when using formula as a cell value

    I think it might be because you are referencing a cell (B5) that has a formula in it, so it is "reading" the formula instead of the number (690) that is in the cell.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom
    Reply With QuoteRegister To Reply
  3. 06-09-2015, 08:45 AM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: median shows wrong result when using formula as a cell value

    I didn't download your file.

    This formula (why so many parentheses?):

    (=(MID(A5;3;3)))

    Returns a TEXT value even if it looks like a number.

    To get it to return a numeric value try it like this:

    =--MID(A5;3;3)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 06-09-2015, 08:45 AM #4
    thucydides
    thucydides is offline
    Registered User
    Join Date
    06-09-2015
    Location
    Croatia
    MS-Off Ver
    2010
    Posts
    8

    Re: median shows wrong result when using formula as a cell value

    I know, and im trying to solve just that. Is there a solution for that?
    Reply With QuoteRegister To Reply
  5. 06-09-2015, 08:58 AM #5
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: median shows wrong result when using formula as a cell value

    Great teamwork Tony.
    Thucydides,
    The solution Tony provided will work, I tested it on your sample.
    Reply With QuoteRegister To Reply
  6. 06-09-2015, 09:00 AM #6
    thucydides
    thucydides is offline
    Registered User
    Join Date
    06-09-2015
    Location
    Croatia
    MS-Off Ver
    2010
    Posts
    8

    Thumbs up Re: median shows wrong result when using formula as a cell value

    Quote Originally Posted by Tony Valko View Post
    ...

    To get it to return a numeric value try it like this:

    =--MID(A5;3;3)
    Thank you very much, you are a lifesaver!
    Reply With QuoteRegister To Reply
  7. 06-09-2015, 09:04 AM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: median shows wrong result when using formula as a cell value

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
    Reply With QuoteRegister To Reply
  8. 06-09-2015, 09:19 AM #8
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: median shows wrong result when using formula as a cell value

    I'd like to point out your median includes the B5 number in your current sheet... if that is not intended, then you might want to fix it, but then the median of C5 and D5 is only 689.5... to solve that there are 2 problems.... your median is actually 689.5 so the condition is not met. Secondly, although you formatted B5 as a number, the mid function is returning a string so you are comparing a number and a string....
    Change B5 formula to:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    and the F3 formula to:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Unless you want 689.5 to actually differ from 690 in which case the formula in F3 is fine.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.
    Reply With QuoteRegister To Reply
  9. 06-09-2015, 09:57 AM #9
    thucydides
    thucydides is offline
    Registered User
    Join Date
    06-09-2015
    Location
    Croatia
    MS-Off Ver
    2010
    Posts
    8

    Re: median shows wrong result when using formula as a cell value

    Arkadi, thanks for your input!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cell shows wrong result
    By jatolo in forum Excel General
    Replies: 3
    Last Post: 06-30-2014, 08:35 PM
  2. [SOLVED] Cell shows only formula, no result
    By alexrm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  3. [SOLVED] Cell shows only formula, no result
    By alexrm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Cell shows only formula, no result
    By Jim Thomlinson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Cell shows only formula, no result
    By alexrm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1