Results 1 to 5 of 5

Thread: Grade compare two non blank cells with alphabets and report trend

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Grade compare two non blank cells with alphabets and report trend

    Dear MrExcelites,

    Greetings, I need a formula to compare the last and the last second cell and report whether it has increased or decreased or stays the same.

    Grade Table:
    BCDEFGHIJKLM
    1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
    2BB

    Sheet1





    Used below formula, but with error.
    Trend Result:
    P
    1TREND
    2#VALUE!

    Sheet1



    Worksheet Formulas
    CellFormula
    P2=IF(OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1)<OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,2),"",IF(OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1)>OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1),"","n"))



    Char used alt codes in formula:
    PQR
    4CHAR MAP USEDCHARUsed for
    5ALT+0233 for increment
    6ALT+0234 for decrement
    7nn for equal

    Sheet1





    will be using wingding format in the resulting cell.

    A simple formual would be:
    R
    1TREND
    2n

    Sheet1



    Worksheet Formulas
    CellFormula
    R2=IF(F2<G2,"",IF(F2>G2,"","n"))



    but I need it to compare last two non blanks in a year.

    PS: tried using aggregate, index too but didnt succeed.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grade compare two non blank cells with alphabets and report trend

    Consider:

    =CHOOSE(SIGN(LOOKUP(2,1/(B2:M2<>0),B2:M2)-LOOKUP(2,1/(B2:M2<>0),A2:L2))+2,"","n","")

    This assumes there are no gaps between the last and second to last values.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grade compare two non blank cells with alphabets and report trend

    Sorry, I missed that you are using letters instead of numbers, try:

    =CHOOSE(SIGN(CODE(LOOKUP(2,1/(B2:M2<>0),B2:M2))-CODE(LOOKUP(2,1/(B2:M2<>0),A2:L2)))+2,"","n","")
    Last edited by Eric W; Jun 1st, 2019 at 11:07 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    New Member
    Join Date
    Jul 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grade compare two non blank cells with alphabets and report trend

    Quote Originally Posted by Eric W View Post
    Sorry, I missed that you are using letters instead of numbers, try:

    =CHOOSE(SIGN(CODE(LOOKUP(2,1/(B2:M2<>0),B2:M2))-CODE(LOOKUP(2,1/(B2:M2<>0),A2:L2)))+2,"","n","")
    Thanks a lot @eric-w works perfectly.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Grade compare two non blank cells with alphabets and report trend

    Happy to help.

Some videos you may like

User Tag List

Tags for this Thread

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
  •