Finding the Right Excel Formula and putting it together

%3CLINGO-SUB%20id%3D%22lingo-sub-2201057%22%20slang%3D%22en-US%22%3EFinding%20the%20Right%20Excel%20Formula%20and%20putting%20it%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2201057%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3CBR%20%2F%3EThis%20is%20my%20first%20time%20using%20this%20forum%20but%20I've%20hit%20a%20roadblock.%20I'm%20trying%20to%20create%20a%20tool%2Fcalculator%20for%20the%20line%20of%20work%20which%20is%20scouting%20football%2Fsoccer%20players%20across%20the%20world%20and%20to%20bring%20them%20to%20the%20UK.%20I'm%26nbsp%3B%20using%20Excel%20which%20part%20of%20my%20Microsoft365%20subscription.%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20table%20for%20review.%20As%20you%20can%20see%20that%20the%20table%20in%20based%20on%20criteria%2C%20if%20both%20percentage%20and%20the%20ranking%20is%20meet%2C%20the%20cell%20should%20give%20the%20output%20which%20is%20highlighted%20in%20green%2C%20orange%20and%20red.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%20player%20x%20plays%20for%20an%20international%20country%20that%20is%20ranked%20number%2012%20in%20the%20world%20and%20he%20has%20achieved%2091%25%20of%20total%20minutes%20played%20in%20matches%20over%20the%20last%202%20years%20(this%20information%20is%20inputted%20in%20different%20cells%20within%20my%20worksheet).%20The%20cell%20where%20I%20want%20the%20result%20to%20be%20in%20should%20give%20me%20an%20Auto%20Pass.%20If%20another%20player%20plays%2025%25%20of%20total%20minutes%20played%20in%20matches%20over%20the%20last%202%20years%20and%20the%20ranking%20of%20the%20team%20is%20number%2C%20it%20should%20give%20me%20an%20answer%20of%2010.%20If%20the%20tanking%20of%20the%20team%20was%2060%20it%20would%20be%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20is%20going%20to%20be%20a%20complex%20formula%20as%20a%20lot%20of%20statements%20are%20required%20to%20build%20the%20formula%2C%20with%20the%20answer%20being%20presented%20in%20a%20single%20cell%2C%20not%20multiple%20cells.%20I%20hope%20this%20explains%20what%20i'm%20trying%20to%20do.%20If%20there%20any%20further%20questions%20please%20do%20let%20me%20know%20but%20once%20i've%20resolved%20this%2C%20this%20can%20help%20me%20throughout%20the%20rest%20of%20the%20tool.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2201057%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2201523%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20Right%20Excel%20Formula%20and%20putting%20it%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2201523%22%20slang%3D%22en-US%22%3EI%20can%20suggest%20you%20a%20method%20to%20achieve%20but%20what%20I%20need%20is%20some%20sample%20data%2C%2C%20like%20Player%20his%20Rank%20%26amp%3B%20Minutes%20he%2Fshe%20played%20year%20wise%2C%2C%2C%20and%20the%20criteria%20for%20putting%20Auto%20and%20others%20like%200%20to%2010%20under%20what%20circumstances%2C%2C%20or%20if%20possible%20please%20share%20the%20WB%20with%20us.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2201695%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20Right%20Excel%20Formula%20and%20putting%20it%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2201695%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F993678%22%20target%3D%22_blank%22%3E%40ozzie4khawaja%3C%2FA%3E%26nbsp%3BSince%20you%20indicated%20to%20be%20a%20MS365%20subscriber%2C%20you%20can%20use%20INDEX%20and%20XMATCH%20to%20achieve%20your%20goal%2C%20fairly%20easily%2C%20as%20demonstrated%20in%20the%20attached%20workbook%20and%20on%20the%20basis%20of%20the%20scoring%20table%20in%20your%20screenshot.%20I%20changed%20the%20column%20and%20row%20headers%20to%20numbers%2C%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2202185%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20Right%20Excel%20Formula%20and%20putting%20it%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2202185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHello%20Rinky%2C%20thank%20you%20so%20much%20for%20providing%20your%20workbook%2C%20it%20seems%20like%20most%20of%20the%20example%20you%20provided%20works%20however%20I%20checked%20that%20if%20the%20player%20scores%205%25%20playing%20time%20with%20a%20ranking%20of%201%2C%20it%20comes%20back%20with%200%20points%20when%20it%20should%20come%20back%20with%208%20points.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All,
This is my first time using this forum but I've hit a roadblock. I'm trying to create a tool/calculator for the line of work which is scouting football/soccer players across the world and to bring them to the UK. I'm  using Excel which part of my Microsoft365 subscription.

I have attached a table for review. As you can see that the table in based on criteria, if both percentage and the ranking is met, the cell should give the output which is highlighted in green, orange or red.

 

So for example player x plays for an international country that is ranked number 12 in the world and he has achieved 91% of total minutes played in matches over the last 2 years (this information is inputted in different cells within my worksheet). The cell where I want the result to be in should give me an Auto Pass. If another player plays 25% of total minutes played in matches over the last 2 years and the ranking of the team is number, it should give me an answer of 10. If the tanking of the team was 60 it would be 0.

 

I know this is going to be a complex formula as a lot of statements are required to build the formula, with the answer being presented in a single cell, not multiple cells. I hope this explains what i'm trying to do. If there any further questions please do let me know but once i've resolved this, this can help me throughout the rest of the tool.

 

Thank you in advance.

 

10 Replies
I can suggest you a method to achieve but what I need is some sample data,, like Player his Rank & Minutes he/she played year wise,,, and the criteria for putting Auto and others like 0 to 10 under what circumstances,, or if possible please share the WB with us.

@ozzie4khawaja Since you indicated to be a MS365 subscriber, you can use INDEX and XMATCH to achieve your goal, fairly easily, as demonstrated in the attached workbook and on the basis of the scoring table in your screenshot. I changed the column and row headers to numbers, though.

@Riny_van_Eekelen Hello Rinky, thank you so much for providing your workbook, it seems like most of the example you provided works however I checked that if the player scores 5% playing time with a ranking of 1, it comes back with 0 points when it should come back with 8 points.

Hi Riny, I've tried to follow your formula and input that into my workbook but my results don't match yours. I'll attach my worksheet to the main post. Your A & B column cells are on the main calculator tab under the first greyed out box and the table is under FIFA Ranking. Can you tell me where I'm going wrong?

@ozzie4khawaja Sorry, you loose me here. I know nothing about football and find the form quite confusing. Kindly fill it in with an example and indicate where the calculation is made and what's wrong.

Ok i'll send you the screenshots for you to see, hopefully that will help
Now attached, i've highlighted the formula i've used, the cells involved and the table which is on another tab. Hopefully this should remove any confusion.

@ozzie4khawaja Since you didn't attach the actual sheet with the formula in it, I did something in the one you did upload earlier.  Attaching it below.

 

Screenshot 2021-03-12 at 16.25.02.png

J2 is a number, H2 is formatted as text (!) and I entered "25%" (without the quotes) and the result is displayed in L2. Seems correct to me. Hopefully you can replicate it in your own workbook.

Hi Riny, thank you so so so much. I've managed to get it to work, finally.