Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Return Lowest Based on Text

1. Established Member
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.

Excel Video Tutorials / Excel Dashboards Reports

2. Have VBA, will travel
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. ## 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. Senior Member
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

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.

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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. Established Member
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. ## 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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

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

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

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