# Rating from Parameter Lookup

Solved/Closed
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015 - Jan 26, 2015 at 03:33 AM
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015 - Jan 30, 2015 at 02:19 AM
Hi..

I have some data that reference parameter to fill Rating in my excel sheet.
The problem is those parameter are in Text Type.

Here is the example

For example, If the "Result" already fill in each Profile & Quarter (column I3, K3, M3, O3), how can I fill it's "Rating" in column J3, L3, N3, P3 based on RATING parameter in column C3:G3? because the Parameter are in Text type so I can't compare it.

Thank you so much for your help, i really appreciate it because it's been almost a week I search for the answer :)

Regards,
Yudi.
Related:

## 2 responses

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jan 26, 2015 at 06:22 PM
Try this multiple if solution:

=IF(100*I3<VALUE(LEFT(RIGHT(\$C\$3,3),2)),"1",IF(100*I3<VALUE(LEFT(RIGHT(\$D\$3,3),2)),"2",IF(100*I3<VALUE(LEFT(RIGHT(\$E\$3,3),2)),"3",IF(100*I3<VALUE(LEFT(RIGHT(\$F\$3,3),2)),"4",IF(100*I3>=VALUE(LEFT(RIGHT(\$G\$3,3),2)),"5","")))))

This will go in cells:
J3, L3, N3, P3

Cell G5 should be change to be >=85% to be consistent and to allow the formula to work.

I wasn't sure what to put in the rating field so I just put 1,2,3,4 or 5 depending on what it found.
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 26, 2015 at 10:28 PM
Hi RayH,

This formula is working with Profile1, but it's not working with Profile2.

Rating Parameter's can be in number or percentage style. I think your formula only working with percentage style :-)

And same with my reply to Trowa, Parameter Rating can be written inconsistently. For example in cell G5 i wrote it "85<=%" and maybe in the other cell i wrote it ">=85%".

I want to make the formula more flexible with the writings.

I'm sorry to get you confused :-)

Yudi
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26 > dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 27, 2015 at 10:46 AM
Here is the formula for Profile 2:

=IF(I4<=VALUE(LEFT(RIGHT(\$C\$4,4),4)),"1",IF(I4<VALUE(LEFT(RIGHT(\$D\$4,4),4)),"2",IF(I4<VALUE(LEFT(RIGHT(\$E\$4,4),4)),"3",IF(I4<VALUE(LEFT(RIGHT(\$F\$4,4),4)),"4",IF(I4>=VALUE(LEFT(RIGHT(\$G\$4,4),4)),"5","")))))

The ratings need to be changed to the below for it to work:
<=1.50 <2.50 <3.50 <4.50 <=4.50
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 28, 2015 at 02:55 AM
Hi RayH,

It works for Profile2, and maybe with a little bit changes, your formulas will work with any other Profiles that I have in my Excel sheet.

Thaks a lot :)

Regards,
Yudi
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 26, 2015 at 12:11 PM
Hi Yudi,

You can use a combination of the formula's RIGHT and VALUE to isolate and convert the numbers portion to a number.

Let me know if you get stuck.

Best regards,
Trowa
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 26, 2015 at 10:14 PM
Hi Trowa,

I already use RIGHT and VALUE formula to get the number compared. But the problem is, what if Rating Parameters are not consistent in the writing. For example, in column G3, i wrote the rating with ">=85%" instead of "85<=%". I must change my formula to get it work again. There are many kinds of way of writing the Rating Parameter. I want to get my formula more flexible so if i change the way i write the formula it still working perfectly.

Is there any other way i can write the formula so i can use it more flexible with writing changes..?

Yudi
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 27, 2015 at 11:43 AM
Hi Yudi,

Not sure how to respond to your request.

Is it too much to ask to enter your data with a certain consistency?

Keep in mind that you can enter your data any way you like, but that formula's have their limitations.

Maybe that RayH has a more satisfying answer for you.

Best regards,
Trowa
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 28, 2015 at 02:43 AM
Hi Trowa,

Actually that will be my last effort to make all rating parameters to be more consistent if there aren't any more flexible formula in excel :)

Regards,
Yudi
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26 > dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015
Jan 28, 2015 at 01:09 PM
Rather than having to worry about the consistency I thought it might be useful use a Customer Function to remove all but the numeric value from a cell.

'Convert a text string to number by removing non-numeric characters
Function text2num(ByVal textvalue As String) As Double
Dim newtext As String
Dim n As Integer
Dim c As Integer

For n = 1 To Len(textvalue)
c = Asc(Mid(textvalue, n, 1))
' is it a number (0-9) or a period "." ?
If (c >= 48 And c <= 57) Or c = 46 Then
newtext = newtext & Mid(textvalue, n, 1)
End If
Next n
text2num = Val(newtext)
End Function

It can be used like this for Profile1:
=IF(100*I3<=text2num(\$C\$3),"1",IF(100*I3<text2num(\$D\$3),"2",IF(100*I3<text2num(\$E\$3),"3",IF(100*I3<text2num(\$F\$3),"4",IF(100*I3<=text2num(\$G\$3),"5","")))))

and this for Profile2:
=IF(I4<=text2num(\$C\$4),"1",IF(I4<text2num(\$D\$4),"2",IF(I4<text2num(\$E\$4),"3",IF(I4<text2num(\$F\$4),"4",IF(I7<=text2num(\$G\$4),"5","")))))

Obviously the < and <= in the formula needs to match what is in the actual field being used to compare values.

Values like this:
123e323s1q

become 1233231

Hope it's of some use to you.
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015 > RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016
Jan 29, 2015 at 11:13 PM
Hi RayH,

I'm sorry to asking you this because it's kinda newbie question :)

How do I use this function..??

Is it same with macro ..??