Create range in base of ratio

Question

I have a big problem to calculate votes with my excel documents. I have some field for a project: - minimum estimate in hours (before I start a project I tell how many hours I think to do for this project) - maximum estimate in hours(before I start a project I tell how many hours maximum I think to do for this project) - hours total (after the complete project I assign the total hours)

In base of this value I wanna create a range start from 1 to 10 where 10 is the minimum value ( minimum estimate in hours) and 6 is the sufficient value ( maximum estimate in hours) and if I take more hours than my maximum votes is negative progressively

Example:

Minimun hours: 10 Maximum hours: 20 Total effective hours: 20 Votes-----> 6

Minimun hours: 10 Maximum hours: 20 Total effective hours: 10 Votes-----> 10

Minimun hours: 10 Maximum hours: 20 Total effective hours: 30 Votes-----> 4(estimate value)

How can I do this range in math? COnsider that I have an excel document to do it.

Thanks

Answer 1

Let $m$ denote the minimum estimate, $M$ the maximum estimate, $T$ the total, $V$ the votes. One approach is $$v=10-\frac{4(T-m)}{M-m}$$

There are two flaws here; one, the result might not be an integer and two, the result might be negative. To fix these, try instead $$v=\mathsf{MAX}\left(1,\;\mathsf{ROUND}\left(10-\frac{4(T-m)}{M-m}\right)\right)$$