Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Return Lowest Based on Text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    I am new to VBA - comments on how to improve my code are always welcome.

  • #2
    Re: Return Lowest Score In A Range Of Values

    Upside,

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

    Code:
    =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",""))))
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

    Comment


    • #3
      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, 15:06.

      Comment


      • #4
        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.

        Code:
        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

        Comment


        • #5
          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
          Kris

          ExcelFox

          Comment


          • #6
            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, 16:29.
            I am new to VBA - comments on how to improve my code are always welcome.

            Comment


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

              Comment


              • #8
                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
                Kris

                ExcelFox

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X