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
dxa02091 Posts 7 Registration date Monday January 26, 2015 Status Member Last seen January 30, 2015 - Jan 30, 2015 at 02:19 AM
Related:
- Rating from Parameter Lookup
- Escape the ayuwoki age rating - Download - Horror
- Https //accounts.google.com/sign in/v1/lookup - Guide
- Slendytubbies age rating - Download - Horror
- Lookup computer name by ip - Guide
- Area code lookup - Guide
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
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
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.
=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.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jan 26, 2015 at 12:11 PM
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
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
Jan 26, 2015 at 10:14 PM
Hi Trowa,
Thanks for your advice.
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..?
Thanks in advance.
Yudi
Thanks for your advice.
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..?
Thanks in advance.
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
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
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
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 :)
But, thanks for your advice.
Regards,
Yudi
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
26
>
dxa02091
Posts
7
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 30, 2015
Jan 28, 2015 at 01:09 PM
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.
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.
'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
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 ..??
Thanks for your kind help
Regards,
Yudi
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
Jan 26, 2015 at 10:28 PM
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
Jan 27, 2015 at 10:46 AM
=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
Jan 28, 2015 at 02:55 AM
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