How to give a ranking to rows based on multiple criteria against weighted factors?

I'm setting up an excel file that lets you input values, and then it will provide a ranking of 1 to 5 on each values, and then compare all values based on a set of weighted values.

It is a business problem that I'm struggling with, because I somehow need to devise ranking system that needs to take into account all factors to decide which of the options I want to do is the best one.

However, some of factors require a different arrangement of the IF function. Because for some of the column, the higher the value = the lower the ranking and vice versa.

I have already set the criteria for rankings 1 to 5 of each factor.

Example: the higher the number of prospects, the higher the ranking. the higher the cost per conversion, the lower the ranking. And so on. Below is an example of the code that I have made: It differs for each column (I will provide a link to the file)

=IF(D7<1," ",IF(D7<=500,"1",IF(D7<=2500,"2",IF(D7<=3750,"3",IF(D7<=5000,"4",IF(D7>5000,"5"))))))

=IF(E7<1," ",IF(E7<=250,"1",IF(E7<=499,"2",IF(E7<=999,"3",IF(E7<=2500,"4",IF(E7>2500,"5"))))))

=IF(F7>7,"1",IF(F7>5,"2",IF(F7>3,"3",IF(F7>1,"4",IF(F7=1,"5",IF(F7<1," "))))))

=IF(H7<1," ",IF(H7<=500000,"1",IF(H7<=1200000,"2",IF(H7<=2500000,"3",IF(H7<=4000000,"4",IF(H7>4000000,"5"))))))

So now we have a new table which includes all the rankings 1 to 5. But, I have not (or will have to) somehow put the weighted factors into the equation to consider the weights (as this will really affect the final ranking results).

This is the part that I struggle the most with, I could not find a function that somehow factor in the weighted values in order to give an overall value.

Thank you in advance for anyone who is able to help me on this.