How to create a ranking logic/formula with multiple metrics in excel

I have a big list of pages for a website that I need to write content for. To prioritise the work, I want to come up with a ranking based on 4 metrics, which are:

Keyword Difficulty: The estimated difficulty to rank high in a Search Engines' results for the keyword people search for. A lower number is better:

0-10 Easy, 11-30 Medium, 31-70 Hard, 71+ Very Hard

Search Volume: The average amount of times a keyword gets searched by users each month. Higher is better.

Pages Current Avg position: The avg position the relevant page on the website is currently ranked in when users are searching for the keyword on a search engine. A lower number is better, as position 1 is the top of the first pages search results and there are generally 10 results per page, so 1-10 is on page one, 11-19 is page two, etc.

Conversation Rate: The rate at which visitors convert on each page to a take an action. Higher is better.




How can I use those 4 metrics to come up with some kind of ranking to prioritise working on the pages?

Looking to prioritise keywords that have a high search volume, a difficulty that isn't too hard, a higher conversation rate and where a page is already ranking higher in avg position. (Not necessarily in that order)

Thank you so much for your solution Shareez.

Are you able to break down exactly what the formula is doing, to help me understand the logic.

THanks

Hey Shahreez, Thanks for the formula. its quite helpful. Wanted to know can I edit the columns and change the criteria. Your help is highly appreciated.