Sep 30 2020 02:28 AM
Hello,
I am new to formula and trying to create one that shows a different result in the column Score "E" every time a number is entered in the Stars column "D".
If 5 = Gold, if 4 = Silver, picked up from the B5, B4, B3 cells, etc...
I'll be grateful if someone could help with the right formula?
Thank you.
Freddy
Sep 30 2020 02:53 AM
SolutionHi @FreddyNoel ,
There are many ways to handle it, please find below you may construct your formula using VLOOKUP. your source data should be in ascending order to get the results.
=VLOOKUP(D4,$A$9:$B$13,2,TRUE)
Attached is the sample file for your reference
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Sep 30 2020 02:55 AM
Nikolino
I know I don't know anything (Socrates)
Sep 30 2020 03:31 AM
Thank you, Faraz, for the quick reply and awesome solution.
I have learnt something and you made my day.
Best regards
Freddy
Sep 30 2020 03:33 AM
Thank you, Nikolino, for your quick reply.
My apologies for inserting a picture instead of the file.
This was my first post and I am already learning.
Best regards
Freddy
Sep 30 2020 03:52 AM
Oct 01 2020 08:04 AM
Thank you both for your input and if I may ask one more favour, it would make my spreadsheet amazing. As per Nikolino recommendation, I attach a file for easier editing.
Is there a possibility to add to the "=VLOOKUP(D9,$A$16:$B$20,2,1)" a complementary formula that would make my Gold rating cell coloured Yellow, Silver rating cell coloured Grey and Bronze rating cell coloured Brown? The colour could either be the text or the fill-in cell.
Thank you again for your amazing support.
Best regards
FreddyNoel
Oct 01 2020 10:31 AM
In the inserted file you can see the formula in action.
in German:
=WENNFEHLER(SVERWEIS(D10;$A$16:$B$20;2);"")
in English:
=IFERROR(VLOOKUP(D10,$A$16:$B$20,2),"")
So you no longer have an error message ... if there is no value in D then E is also empty.
Hope I was able to help you and would be happy to know if I could help you.
Nikolino
I know I don't know anything (Socrates)
Oct 01 2020 12:33 PM
Awesome! Thank you so much, Nikolino for this unsolicited tip which makes my spreadsheet look neat and so much more professional!
I had no idea one could do that, and again learnt something brilliant today.
I am still wondering if Excel can do the function of adding colour for my Gold, Silver and Bronze cells? Maybe that is not possible, and it would not be a problem if I cannot do that.
Thank you again for your great knowledge and kind assistance.
Best regards
FreddyNoel
Oct 01 2020 01:00 PM
Here is the file with the desired colors.
At the same time information on how to do conditional formatting in Excel
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Oct 02 2020 11:59 AM
Hello, Nikolino,
Sorry for my late acknowledgement as I had a busy day.
FANTASTIC! I am in awe by your knowledge and kind contribution to making my spreadsheet an absolute gem.
I would not have been able to do it without your help.
The link to the Conditional Formatting is brilliant and very clear.
A million thanks again.
Best regards
FreddyNoel
Oct 02 2020 01:24 PM
Oct 23 2020 09:09 AM
Hello Nikolino,
I am stuck again after finding out that I made a mistake with my ratings.
If you can help again, please, I would be much grateful.
In the column Total M, each member's Total should be the sum of the TWO HIGHEST Scores ONLY, out of 3 marks.
For example, Member 1 Total should be 1.25 and Member 2 shall be 2.50
I have tried but lamentably failed to find the right formula.
Thank you if you can offer a solution.
Best regards
Frederic
Oct 23 2020 11:39 AM
Add this formula into cell M4 and drag the cell down (to the next cell, etc.)
See the attached file.
German formula:
=KGRÖSSTE(J4:J6;1)+KGRÖSSTE(J4:J6;2)
English formula:
=LARGE(J4:J6,1)+LARGE(J4:J6,2)
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Oct 25 2020 12:02 PM
A million thanks again, Nikolino.
You are a Wizard, doing magic with Excel.
Best regards
Frederic
Oct 26 2020 12:10 AM
Sep 30 2020 02:53 AM
SolutionHi @FreddyNoel ,
There are many ways to handle it, please find below you may construct your formula using VLOOKUP. your source data should be in ascending order to get the results.
=VLOOKUP(D4,$A$9:$B$13,2,TRUE)
Attached is the sample file for your reference
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more