Taking multiple values from one cell and rating them in one cell with a 1 - 10 point scale system.

Andu

New Member
Joined
Jul 30, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I'm wondering if there's any way to put multiple different values into one cell and separating them with some kind of symbol, for example "\" like seen on the picture and then reading those values to another cell, rating them individually from 1 - 10 and then displaying them in one cell and separating with the same symbol as used before. The function I use for rating is (example for cell C2): =1+9*(B2-MIN(B$2:B$8))/(MAX(B$2:B$8)-MIN(B$2:B$8)) which basically rates the minimum value as 1 and the maximum as 10 and rest of the values anywhere from 1 - 10. The first picture is what I have now and the second picture is what I'd like to have.
 

Attachments

  • Screenshot 2023-06-17 015126.png
    Screenshot 2023-06-17 015126.png
    8 KB · Views: 14
  • Screenshot 2023-06-17 015155.png
    Screenshot 2023-06-17 015155.png
    6.8 KB · Views: 12

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
For separating them, you could use the search feature.
Fore example, assuming 54\11 is in B2, you can use this to get just the 54:
Excel Formula:
=LEFT(B2,SEARCH("\", B2)-1)

and use this to get the 11 from B2:
Excel Formula:
=MID(B2, SEARCH("\",B2)+1, LEN(B2)-SEARCH("\", B2))

You could put that part of the formula in some other formula.
 
Upvote 1
For separating them, you could use the search feature.
Fore example, assuming 54\11 is in B2, you can use this to get just the 54:
Excel Formula:
=LEFT(B2,SEARCH("\", B2)-1)

and use this to get the 11 from B2:
Excel Formula:
=MID(B2, SEARCH("\",B2)+1, LEN(B2)-SEARCH("\", B2))

You could put that part of the formula in some other formula.
Thanks!
 
Upvote 0
Welcome to the MrExcel board!

I'm wondering if there's any way to put multiple different values into one cell and separating them with some kind of symbol, for example "\" like seen on the picture and then reading those values to another cell, rating them individually from 1 - 10 and then displaying them in one cell and separating with the same symbol as used before.
Like this?

Cell Formulas
RangeFormula
C2:C8C2=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,(1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L)))&"/"&(1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))))
 
Upvote 1
Solution
Welcome to the MrExcel board!


Like this?

Cell Formulas
RangeFormula
C2:C8C2=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,(1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L)))&"/"&(1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))))
Yes! Thank you very much. Is there a way to have it display the rating with up to max of 3 decimal places (excluding whole numbers)?
 
Upvote 0
Is there a way to have it display the rating with up to max of 3 decimal places (excluding whole numbers)?
Sure

Andu.xlsm
BC
1
254/117.088/10
331/41/1
431/41/1
544/64.441/3.571
631/41/1
765/910/7.429
865/910/7.429
Sheet1.1
Cell Formulas
RangeFormula
C2:C8C2=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,ROUND((1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L))),3)&"/"&ROUND((1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))),3))
 
Upvote 1
Sure

Andu.xlsm
BC
1
254/117.088/10
331/41/1
431/41/1
544/64.441/3.571
631/41/1
765/910/7.429
865/910/7.429
Sheet1.1
Cell Formulas
RangeFormula
C2:C8C2=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,ROUND((1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L))),3)&"/"&ROUND((1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))),3))
Perfect! Appreciate it!
 
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top