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.

 

[lastminuteproject]

I think my explanation was a little bit messy.

Another example:
London - 3 days
Manchester - 1.7 day
Chester - 0.3 days
TOTAL - 5 days

How I can tune the values to be as minimum 1 day but having always at the end the same total days?

 

[Gerald Higgins]

You could use Data Validation to either force 1 as a minimum, or force input of whole numbers, and not allowing decimals.

Then, you could set one of your values - in this example perhaps Chester - to be a balancing figure, that is calculated by a formula which takes the total and subtracts from it the data already entered for the other values.

 

[lastminuteproject]

Yes, that could solve small examples. But sometimes I have tables with 52 cities, If only one is balancing the result will be not so real (maybe will be substracted more than 3 or 4 days). Can I put all as a Balanced cells? I will work on it. Lets see.

Thank you for the help

 

[jasonb75]

Maybe you could utilise something like

Excel Workbook
	A	B	C
2	london	3	3
3	manchester	1.7	1
4	chester	0.3	1
5	total	5	5
Sheet5

Note that due to the circular references you would need to enable iterative calculation in excel formula options.

 

[lastminuteproject]

Thank you a lot!

I will try and I will let you know

 

[lastminuteproject]

Thank you Jason, but rounding is not enough, should be able to substract in order to match the total case.

I will try later to substitute the round parameter for a substraction, lets see. Attached I let the data which I'm working now. As you can see the formula returns a higher total value.

I will keep traying.

Thank you all for your help.

PS: As you can see the minimun value that I set in this example was 0.3

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0">
<col width=64 span=3 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7629568 width=64 style='height:15.0pt;width:48pt'>A</td>
<td class=xl7629568 width=64 style='width:48pt'>B</td>
<td class=xl7629568 width=64 style='width:48pt'>C</td>

</tr>
<tr height=81 style='height:60.75pt'>
<td height=81 class=xl6529568 width=64 style='height:60.75pt;width:48pt'>Cities</td>
<td class=xl6529568 width=64 style='border-left:none;width:48pt'>Corresponding
Days/City</td>
<td class=xl6629568 width=64 style='border-left:none;width:48pt'>Final
Days/City</td>
</tr>
<tr height=21 style='height:15.75pt'>

<td height=21 class=xl7329568 style='height:15.75pt;border-top:none'>City 1</td>
<td class=xl6929568 width=64 style='width:48pt'>0.38</td>
<td class=xl6729568 width=64 style='border-left:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 2</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.99</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 3</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.00</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 4</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.79</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 5</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.47</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 6</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.24</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 7</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.63</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 8</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>11.02</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>12.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 9</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.76</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 10</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.82</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 11</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.78</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 12</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.73</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 13</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.40</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 14</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.59</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 15</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.71</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 16</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.84</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 17</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.24</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 18</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.18</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 19</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.96</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 20</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.60</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 21</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.33</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 22</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.62</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 23</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.35</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 24</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.89</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 25</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.45</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 26</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.30</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 27</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.51</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 28</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.55</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 29</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.86</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 30</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>1.82</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>2.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 31</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.10</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 32</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.06</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 33</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.03</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 34</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.07</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 35</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.10</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 36</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.13</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 37</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.18</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 38</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.22</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 39</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.19</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 40</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.12</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 41</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.09</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 42</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.12</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>

<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 43</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.11</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 44</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.25</td>

<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='page-break-before:always;height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 45</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.22</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>

<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 46</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.21</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 47</td>

<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.40</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 48</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.08</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>

</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7429568 style='height:15.0pt;border-top:none'>City 49</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.08</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>0.3</td>
</tr>
<tr height=21 style='height:15.75pt'>

<td height=21 class=xl7429568 style='height:15.75pt;border-top:none'>City 50</td>
<td class=xl7029568 width=64 style='border-top:none;width:48pt'>0.32</td>
<td class=xl6729568 width=64 style='border-top:none;width:48pt'>1.0</td>
</tr>
<tr height=22 style='height:16.5pt'>
<td height=22 class=xl7429568 style='height:16.5pt;border-top:none'>City 51</td>
<td class=xl7129568 width=64 style='width:48pt'>0.11</td>

<td class=xl6729568 width=64 style='border-top:none;border-left:none;
width:48pt'>0.3</td>
</tr>
<tr height=23 style='height:17.25pt'>
<td height=23 class=xl7529568 style='height:17.25pt;border-top:none'> </td>
<td class=xl7229568>42.00</td>
<td class=xl6829568 style='border-left:none'>59.00</td>
</tr>

<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

 

[jasonb75]

How are the criteria made up?

Calculated days = ??

Min days per city = ??

Max days per city = ??

Will the final criteria always be a minimum of 1 day per city of can it be less based in the other criteria?

 

[lastminuteproject]

Hi Jason,

The criteria is to chosse the ammount of days per city depending on it's population and the total days of the project (total days is a fixed value)

In this case the total days are 42. The problem is when raise another criteria that says that the minimun days per city should be 1 WITHOUT CHANGING THE TOTAL AMOUNT OF DAYS OF THE PROJECT this is making me crazy.

Until now I'm making this "tunning" manually, but I hope that we can find something automatic.

Thank you all for your answers

 

[lastminuteproject]

Hi,

Another example of what I want to achieve.

Total days: fixed value of 42 days
Minimum days per city: Fixed value of 0.5
DAys per city: calculated depending the amount of population of the city and the total days of the project

I made one table more. Column C are the calculated days per city, column D are the tuned days...that I made manually

Any idea? The aproximation from Jason was not bad, but changes the total days (as I show in a prior post...by the way sorry for the bad edition of that post) and that is not possible.

Thank you all in advance

Excel Workbook
	B	C	D
2	City	Calculated Days/City	Tunned Days/City
3	City 1	0.38	0.5
4	City 2	0.99	0.5
5	City 3	1.00	1.0
6	City 4	0.79	0.5
7	City 5	0.47	0.5
8	City 6	1.24	1.0
9	City 7	1.63	1.0
10	City 8	11.02	10.0
11	City 9	0.76	0.5
12	City 10	1.82	1.0
13	City 11	1.78	1.0
14	City 12	1.73	1.0
15	City 13	0.40	0.5
16	City 14	0.59	0.5
17	City 15	1.71	1.0
18	City 16	1.84	1.0
19	City 17	0.24	0.5
20	City 18	1.18	1.0
21	City 19	0.96	1.0
22	City 20	0.60	0.5
23	City 21	1.33	1.0
24	City 22	0.62	0.5
25	City 23	0.35	0.5
26	City 24	0.89	1.0
27	City 25	0.45	0.5
28	City 26	0.30	0.5
29	City 27	0.51	0.5
30	City 28	0.55	0.5
31	City 29	0.86	1.0
32	City 30	1.82	1.0
33	City 31	0.10	0.5
34	City 32	0.06	0.5
35	City 33	0.03	0.5
36	City 34	0.07	0.5
37	City 35	0.10	0.5
38	City 36	0.13	0.5
39	City 37	0.18	0.5
40	City 38	0.22	0.5
41	City 39	0.19	0.5
42	City 40	0.12	0.5
43	City 41	0.09	0.5
44	City 42	0.12	0.5
45	City 43	0.11	0.5
46	City 44	0.25	0.5
47	City 45	0.22	0.5
48	City 46	0.21	0.5
49	City 47	0.40	0.5
50	City 48	0.08	0.5
51	City 49	0.08	0.5
52	City 50	0.32	0.5
53	City 51	0.11	0.5
54	Total	42.00	42.00
Sheet1