# Create range in base of ratio

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

• This was also posted to mathematica.SE. Please note that crossposting between SE sites is highly frowned upon - try one site first, and if you don't get a satisfactory response, ask a moderator to migrate the question to a different site. If you insist on posting in many sites, at least provide links to the other posts - as you can imagine, it would be frustrating for someone to put time into answering your question here, only to find out that you'd already gotten the solution elsewhere. Jul 21, 2013 at 17:32
• Ok I have deleted the post in the other site and now? @ZevChonoles How can I have help for this problem? Jul 21, 2013 at 17:37

## 1 Answer

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)$$

• man this works perfectly! Thanks a lot! Jul 21, 2013 at 17:53
• My pleasure, glad to help. Jul 21, 2013 at 19:09