Looking for a rentability expression on excel

Question

I'm looking for an expression who tell me which "City" got the best "price" in terms of "miles".
The best "city" is where "price" and "miles" are lower. Here my table :

Answer 1

You'll need to choose weights for this to make sense. We can set the weight of miles to 1 and then you can choose some other weight (greater than 1, since you say you care more about price) for price. Put this in, say, F5. Then for Valence (which I'll assume is in A3, you can adjust as needed) type this formula in D3:

=B3 + $F$5*C3

Or nicer still, make a named range named priceWeight so you can write

=B3 + priceWeight*C3

Now drag this formula down across all rows. You can find the minimum with =MIN(D3:D8) (replace with the range of the formulas you created above) and then find the city/cities with the lowest weight. You can even flag them with a formula or conditional highlighting if you want.

The weight should be thought of as "how many additional miles are you willing to take on to save $1" (or whatever unit of currency you're using). With these numbers Nice will be best if the weight is very low, Valence will be best if the weight is very high, and you may get others with intermediate weights.

Answer 2

Just add a column next to price and use formula"=(miles/price)" and then use min function to get the minimum value from the newly created column and can use index match function for finding the most economical city