Rating from Parameter Lookup [Solved/Closed]

Report
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015
-
dxa02091
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015
-
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.

2 replies

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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

Adjust I3 accordingly.

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.
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3989 users have said thank you to us this month

dxa02091
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015

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

Thanks in advance,
Yudi
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24 > dxa02091
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015

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

Hi RayH,

Thanks for your help.

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
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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

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

But, thanks for your advice.

Regards,
Yudi
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24 > dxa02091
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015

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

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 ..??

Thanks for your kind help

Regards,
Yudi
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

Hi RayH,

Correction.....I think i already figure it out :)

Thanks,
Yudi