+ Reply to Thread
Results 1 to 7 of 7

Formula not recognising values in cells?

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    14

    Formula not recognising values in cells?

    I have a student data sheet which feeds into a tracking proforma (Example attached).
    Part of the proforma checks the current scores and uses it to measure the progress the student has made along a scale: 1-, 1, 1+, 2-, 2, 2+, 3-, 3, 3+etc.
    The formula
    =IFERROR((MATCH(G2,$L$2:$L$22,0))-(MATCH(C2,$L$2:$L$22,0)),"")
    does not seem to recognise certain cell values (1, 2, 3...) while it will happily recognise the 1-, 2+ type cells, so I am left with blank boxes in the progress column.

    I feel this is something very obvious but can't spot it, any guidance much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula not recognising values in cells?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You also need to format the list as Text and re-enter the values.


    Regards, TMS
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Formula not recognising values in cells?

    Try

    H2=IFERROR(IFERROR(IFERROR((MATCH(G2,$L$2:$L$22,0))-(MATCH(C2,$L$2:$L$22,0)),(MATCH(G2+0,$L$2:$L$22,0))-(MATCH(C2+0,$L$2:$L$22,0))),(MATCH(G2+0,$L$2:$L$22,0))-(MATCH(C2,$L$2:$L$22,0))),(MATCH(G2,$L$2:$L$22,0))-(MATCH(C2+0,$L$2:$L$22,0))) and drag down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula not recognising values in cells?

    h2
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Contributor
    Join Date
    02-07-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Formula not recognising values in cells?

    or if you dont want to change the cell format, just add "" in match range (TMS's formula), enter with CSE:

    =IFERROR((MATCH(G2&"",$L$2:$L$22&"",0))-(MATCH(C2&"",$L$2:$L$22&"",0)),"")

    Blessing

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Formula not recognising values in cells?

    Thank you, one and all. Most definitely SOLVED!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula not recognising values in cells?

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel not recognising cell values (urgent help)
    By GEOTGA in forum Excel General
    Replies: 7
    Last Post: 01-20-2015, 06:56 PM
  2. Replies: 8
    Last Post: 07-06-2014, 08:51 PM
  3. IF formula not recognising number
    By ddub25 in forum Excel General
    Replies: 2
    Last Post: 06-23-2012, 06:46 AM
  4. [SOLVED] formula recognising text and numbers
    By james1965 in forum Excel General
    Replies: 5
    Last Post: 05-23-2012, 10:34 AM
  5. Replies: 5
    Last Post: 02-18-2012, 04:40 AM
  6. Not recognising two values are the same
    By Big Mark in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2008, 07:52 AM
  7. [SOLVED] Function Not Recognising Values in Populated Ranges
    By Matt Roberts in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2006, 11:35 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1