Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Return Lowest Based on Text

  1. #1
    Join Date
    2nd October 2006
    Posts
    326

    Return Lowest Based on Text

    I am looking for a formula that will return the lowest value in a range, expressed as a letter.

    The relative values of these letters are:

    F=0
    P=1
    M=2
    D=3

    For example,

    If in cells A1:A4 we entered the values F, P, M & D in A5 the result would be F (as it is the lowest value in the A1:A4 range).

    I have included an example worksheet with fictional data and the intended result in the Final Score column.

    Thanks in anticipation.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Return Lowest Score In A Range Of Values

    Upside,

    This is a little long but it does exactly what you need:

    VB:
    =If(Or(A3 ="F",B3="F",C3 = "F",D3="F"),"F",If(Or(A3 ="P",B3="P",C3 = "P",D3="P"),"P",If(Or(A3 ="M",B3="M",C3 = "M",D3="M"),"M",If(Or(A3 ="D",B3="D",C3 = "D",D3="D"),"D","")))) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th October 2006
    Posts
    78

    Re: Return Lowest Score In A Range Of Values

    ...or if you don't want to use VBA...
    Enter a formula and 2 lookup ranges.

    Unfortunately, It's impossible to enclose any files.


    Cheers
    prsthlm
    Last edited by Dave Hawley; November 8th, 2006 at 15:06.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th October 2006
    Location
    Australia
    Posts
    287

    Re: Return Lowest Score In A Range Of Values

    Upside,

    This one is a bit more flexible, allowing you to change the grades and the number of columns that you are grading.
    It does require a bit of VBA knowledge though.

    I've attached your revised workbook as a guide.

    VB:
    Public Function Grading(ByRef rResult As Range, ByRef rRelVals As Range) As String 
         
        Dim c As Range 
        Dim iValue As Integer 
        Dim iKeptValue As Integer 
        Dim iLoop As Integer 
         
        For Each c In rResult.Cells 
            iValue = Application.WorksheetFunction.VLookup(c.Value, rRelVals, 2, False) 
            If iLoop = 0 Then iKeptValue = iValue 
            If iValue <= iKeptValue Then 
                iKeptValue = iValue 
            End If 
            iLoop = iLoop + 1 
        Next 
         
        Grading = GetCellValue(iKeptValue, rRelVals) 
         
    End Function 
     
    Private Function GetCellValue(ByRef iKeptValue As Integer, ByRef rRelVals As Range) As String 
        Dim c As Range 
         
        For Each c In rRelVals.Columns(2).Cells 
            If iKeptValue = c.Value Then 
                GetCellValue = c.Offset(0, -1).Value 
                Exit Function 
            End If 
        Next 
         
    End Function 
    
    
    G.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Return Lowest Score In A Range Of Values

    =lookup(min(choose({1,2,3,4},lookup(a3,{"d","f","m","p"},{3,0,2,1}),lookup(b3,{"d","f","m","p"},{3,0,2,1}),lookup(c3,{"d","f","m","p"},{3,0,2,1}),lookup(d3,{"d","f","m","p"},{3,0,2,1}))),{0,1,2,3},{"f","p","m","d"})

    Hth

  6. #6
    Join Date
    2nd October 2006
    Posts
    326

    Re: Return Lowest Based on Text

    Thanks guys, I really want to try and stick with formulas if at all possible.

    Krishna, is there any way to modify your formula

    =lookup(min(choose({1,2,3,4},lookup(a3,{"d","f","m","p"},{3,0,2,1}),lookup(b3,{"d","f","m","p"},{3,0,2,1}),lookup(c3,{"d","f","m","p"},{3,0,2,1}),lookup(d3,{"d","f","m","p"},{3,0,2,1}))),{0,1,2,3},{"f","p","m","d"})

    so that the result of the formula only appears when data is entered into all 4 previous cells, ie A3,B3,C3,D3? If one (or more) cells in the range are blank the result is also blank.

    Cheers : D
    Last edited by Upside; November 8th, 2006 at 16:29.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Return Lowest Based on Text

    =IF(COUNTA(A3:D3)<>4,0,LOOKUP(MIN(CHOOSE({1,2,3,4},LOOKUP(A3,{"d","f","m","p"},{3,0,2,1}),LOOKUP(B3,{"d","f","m","p"},{3,0,2,1}),LOOKUP(C3,{"d","f","m","p"},{3,0,2,1}),LOOKUP(D3,{"d","f","m","p"},{3,0,2,1}))),{0,1,2,3},{"f","p","m","d"}))

    Hide Zeros via Tools>Options>View-Zero Values

  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Return Lowest Based on Text

    OK. Try,

    =IF(COUNTA(A3:D3)=4,LOOKUP(MIN(CHOOSE({1,2,3,4},LOOKUP(A3,{"D","F","M","P"},{3,0,2,1}),LOOKUP(B3,{"D","F","M","P"},{3,0,2,1}),LOOKUP(C3,{"D","F","M","P"},{3,0,2,1}),LOOKUP(D3,{"D","F","M","P"},{3,0,2,1}))),{0,1,2,3},{"F","P","M","D"}),"")

    HTH

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Return Text Based On Lookup Value
    By ScottMc in forum Excel General
    Replies: 4
    Last Post: March 6th, 2008, 12:41
  2. Return Text Based On Another Cells Value
    By RealEstateFinan in forum Excel General
    Replies: 6
    Last Post: January 22nd, 2008, 10:23
  3. Return Text Based On Text Existing In Range
    By youngsie81 in forum Excel General
    Replies: 4
    Last Post: October 15th, 2007, 08:41
  4. Return 3 Lowest/Smallest
    By airseller in forum Excel General
    Replies: 10
    Last Post: October 10th, 2006, 20:25
  5. Replies: 7
    Last Post: November 30th, 2004, 15:08

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
  •  
porno