Tune a value depending on two cells, challenge your knwoledge!!

[lastminuteproject]

Hi people,

Hi have a challenge for all of you

I'm making some country planning due that my company need to make measurements there.

In the specification is setted the total ammount of days per country and the minimun days per city.

So, once I know more or less the days per city in the country, how can I tune it to, if it has less than one day, change the value for 1 and tune the other cities to don't change the final time per country.

Lets try to put it in an excel view.

ROW 0 - COLUMN A - COLUMN B - COLUMN C - COLUMN D
ROW 1 - City - Population - Calculated days - Tuned days
ROW 2 - City - Population - Calculated days - Tuned days
ROW 3 - X - Total Population - Total days - Total tuned days

The calculated days depend on the ssize of the city and the total days per country. Now I need to tune these days in order to respect a "min" and "max" days per city value. I know how to make this, but the problem is that I don't know how to make that the end value (total tunned days) don't change also. At the end, both values (total days and total tuned days) should be the same.

I know that is a little bit tricky to explain, but I hope than all you understand what i mean. If not, just let me know and I will try to make it clearer

I need to do this a lot in my work, and until now I'm making the tuned days manually

Hope that someone can help me.

Thank all of you in advance.

 

[SamTYler]

Algorithm only:

Tuned column = Round all Up and Down
Difference = Total(Calculated) - Total(Tuned)
 
'Make sure all Cels at least = Min
 
For each Cel in Tuned
   If Cel.Value = 0 Then
      Cel.Value = Cel.Value + Min
      Difference = Difference - Min
 Next Cel
 
Sort Tuned Descending
 
'Get rid of Difference so Total does not change
'Spread Difference across largest values
If Difference > 0
   For each Cel in Tuned
      While Difference <> 0
         Cel.Value = Cel.Value + Min
         Difference = Difference - Min
      Wend
   Next Cel
Else
   For each Cel in Tuned
      While Difference <> 0
         Cel.Value = Cel.Value - Min
         Difference = Difference + Min
      Wend
   Next Cel

 

[lastminuteproject]

uoo

Then that reach I level out of my understanding.

Sorry that I ask this, but since it will save me a lot of time, how/where I should put this code to work? A small explanation will be nice.

Sorry for the bunch of questions and thank you a lot for your help.

 

[SamTYler]

Sorry, that is not code. It is an algorithm, or a code like description of what the actual code needs to do.

I will be leaving this site in a couple of minutes and may or may not be back next weekend.

There are a lot of people here who can take that and run with it, or they may know a better way to accomplish what you want.

 

[lastminuteproject]

Thank you anyway for the hints