Distributing a number/volume across multiple cells where the max possible value of each cell can be defined.

[Excruciating]

I've a list of servers (rows) that I'd like to spread a total load across.

Each has a max load, & a recommended % load threshold to prevent issues. Different servers have different max loads.

(Note... For the non server nerds equally this could work for team members and tasks rather than servers)

My aim is to create a model to spread the load evenly across each server up until it's max threshold. I start with an average across the number of servers I have.

On any one server where the average load is greater than it's threshold (max * recommended %) I want it to only display that maximum load ie not the average. This bit I have sorted.

The tricky but is where I want the remaining 'surplus' on an otherwise loaded server to get added into a remaining bucket of 'load' that then gets added to the original average and applied to the remaining devices.

Small are filled, & larger servers take a greater proportion of total load, possibly even being under utilised.

I can then model the impact of swapping in diff sized servers to my hearts content.

The problem is that I keep getting circular refs when trying to apply that surplus figure!

Any thoughts? Thanks

 

[iliace]

Possibly, you might be able to use Solver to arrive at a solution. Or, if your model is set up to handle iterative calculations, you could turn on circular references?

How are you measuring server load, is it just a number between 1.0 and n.0, where n is the number of CPUs on a given server? I am having trouble coming up with a mock data sample to match your scenario.

Sheet1

*	A	B	C	D	E
Server1
Server2
Server3
Server4
Server5
Server6
Server7
Server8
Server9

<colgroup><col style="font-weight:bold; *******3**** "><col style="*******72px;"><col style="*******9****"><col style="*******109px;"><col style="*******109px;"><col style="*******106px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Server[/TD]
[TD="align: center"]Max Load[/TD]
[TD="align: center"]Threshold[/TD]
[TD="align: center"]Recommended[/TD]
[TD="align: center"]Optimal[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3.00[/TD]
[TD="align: right"]54.50%[/TD]
[TD="align: right"]1.64 [/TD]
[TD="align: right"] 1.64 [/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]4.00[/TD]
[TD="align: right"]77.90%[/TD]
[TD="align: right"]3.12 [/TD]
[TD="align: right"] 2.56 [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4.00[/TD]
[TD="align: right"]54.05%[/TD]
[TD="align: right"]2.16 [/TD]
[TD="align: right"] 2.16 [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]3.00[/TD]
[TD="align: right"]76.21%[/TD]
[TD="align: right"]2.29 [/TD]
[TD="align: right"]2.29 [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1.00[/TD]
[TD="align: right"]71.86%[/TD]
[TD="align: right"]0.72 [/TD]
[TD="align: right"]0.72 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3.00[/TD]
[TD="align: right"]55.22%[/TD]
[TD="align: right"]1.66 [/TD]
[TD="align: right"] 1.66 [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1.00[/TD]
[TD="align: right"]55.02%[/TD]
[TD="align: right"]0.55 [/TD]
[TD="align: right"]0.55 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2.00[/TD]
[TD="align: right"]62.09%[/TD]
[TD="align: right"]1.24 [/TD]
[TD="align: right"] 1.24 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2.00[/TD]
[TD="align: right"]62.25%[/TD]
[TD="align: right"]1.24 [/TD]
[TD="align: right"] 1.24 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =B2*C2 E2 =MIN(D2, AVERAGE($B$2:INDEX(B:B, COUNTA(B:B)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Excruciating]

Thanks Iliace. </br> I posted that from my phone last night so let me add some sample data that might help explain this further. </br> We're almost there but what we're missing from your suggestion is we want to spread the 'observed load' across the machines. Eg number of transactions it can handle. </br> So, if we had a total 'observed load' of 50 transactions, then this needs to be spread across enough servers of the right size to take that load. Ie the servers in the model must have a combined storage of at least 50, plus the recommended overhead on each box (threshold). With this I can then see the impact of dropping one of the boxes out of the model. </br> Will the remaining servers still be able to handle that load, or do we run into problems where the distributed load has maxed out all servers and there's still some left as 'undistributed load'. Does that make more sense? </br> Excel 2013 64 bit

	A	B	C	D	E
Server1
Server2
Server3
Server4
Server5
Server6
Server7
Server8
Server9
Total Load to be distributed (eg workorders / bandwidth / transactions)

<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead> </thead><tbody> [TD="align: center"]1[/TD] [TD="align: center"]Server[/TD] [TD="align: center"]Max Load[/TD] [TD="align: center"]Threshold[/TD] [TD="align: center"]Recommended[/TD] [TD="align: center"]Optimal[/TD] [TD="align: center"]2[/TD] [TD="align: right"]20[/TD] [TD="align: right"]85%[/TD] [TD="align: right"]17.0[/TD] [TD="align: right"]6.7[/TD] [TD="align: center"]3[/TD] [TD="align: right"]10[/TD] [TD="align: right"]85%[/TD] [TD="align: right"]8.5[/TD] [TD="align: right"]6.7[/TD] [TD="align: center"]4[/TD] [TD="align: right"]10[/TD] [TD="align: right"]85%[/TD] [TD="align: right"]8.5[/TD] [TD="align: right"]6.7[/TD] [TD="align: center"]5[/TD] [TD="align: right"]5[/TD] [TD="align: right"]90%[/TD] [TD="align: right"]4.5[/TD] [TD="align: right"]4.5[/TD] [TD="align: center"]6[/TD] [TD="align: right"]5[/TD] [TD="align: right"]90%[/TD] [TD="align: right"]4.5[/TD] [TD="align: right"]4.5[/TD] [TD="align: center"]7[/TD] [TD="align: right"]5[/TD] [TD="align: right"]90%[/TD] [TD="align: right"]4.5[/TD] [TD="align: right"]4.5[/TD] [TD="align: center"]8[/TD] [TD="align: right"]2[/TD] [TD="align: right"]60%[/TD] [TD="align: right"]1.2[/TD] [TD="align: right"]1.2[/TD] [TD="align: center"]9[/TD] [TD="align: right"]2[/TD] [TD="align: right"]60%[/TD] [TD="align: right"]1.2[/TD] [TD="align: right"]1.2[/TD] [TD="align: center"]10[/TD] [TD="align: right"]1[/TD] [TD="align: right"]60%[/TD] [TD="align: right"]0.6[/TD] [TD="align: right"]0.6[/TD] [TD="align: center"]11[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: center"]12[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"]50[/TD] </tbody>

Sheet1​

Worksheet Formulas

<thead> [TH="******* 10px"]Cell[/TH] [TH="align: left"]Formula[/TH] </thead><tbody> [TH="******* 10px, bgcolor: #DAE7F5"]D2[/TH] [TD="align: left"]=B2*C2[/TD] [TH="******* 10px, bgcolor: #DAE7F5"]E2[/TH] [TD="align: left"]=MIN( D2, AVERAGE($B$2:$B$10:INDEX(B2:B10, COUNTA(B2:B10))) )[/TD]

 

[iliace]

Maybe. Check me if I got the logic. Strategy is, distribute "optimal load" to each server first, then distribute the rest as a ratio of excess Recommended over Optimal. Anything above that is either undistributed, or you could use a similar rule to go up to Max Load if desired.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Segoe UI,Arial; font-size:11pt; background-color:#ffffff; padding-******2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; *******3**** " /><col style="*******72px;" /><col style="*******9****" /><col style="*******109px;" /><col style="*******109px;" /><col style="*******106px;" /><col style="*******10****" /><col style="*******72px;" /><col style="*******105px;" /><col style="*******99px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">Server</td><td style="font-weight:bold; text-align:center; ">Max Load</td><td style="font-weight:bold; text-align:center; ">Threshold</td><td style="font-weight:bold; text-align:center; ">Recommended</td><td style="font-weight:bold; text-align:center; ">Optimal</td><td style="font-weight:bold; text-align:center; ">Distribution</td><td style="font-weight:bold; text-align:center; ">60</td><td style="font-weight:bold; text-align:center; ">Suboptimal</td><td style="font-weight:bold; text-align:center; ">Total Load</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Server1</td><td style="text-align:right; ">20.00</td><td style="text-align:right; ">85.00%</td><td style="text-align:right; "> * * * * * * * * *17.0 </td><td style="text-align:right; "> * * * * * * * * * 6.7 </td><td style="text-align:right; "> * * * * * * * *6.67 </td><td style="text-align:right; "> * * * 53.33 </td><td style="text-align:right; "> * * * * * * * 10.33 </td><td style="text-align:right; "> * * * * * * 17.00 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Server2</td><td style="text-align:right; ">10.00</td><td style="text-align:right; ">85.00%</td><td style="text-align:right; "> * * * * * * * * * *8.5 </td><td style="text-align:right; "> * * * * * * * * * 6.7 </td><td style="text-align:right; "> * * * * * * * *6.67 </td><td style="text-align:right; "> * * * 46.67 </td><td style="text-align:right; "> * * * * * * * * 1.83 </td><td style="text-align:right; "> * * * * * * * 8.50 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Server3</td><td style="text-align:right; ">10.00</td><td style="text-align:right; ">85.00%</td><td style="text-align:right; "> * * * * * * * * * *8.5 </td><td style="text-align:right; "> * * * * * * * * * 6.7 </td><td style="text-align:right; "> * * * * * * * *6.67 </td><td style="text-align:right; "> * * * 40.00 </td><td style="text-align:right; "> * * * * * * * * 1.83 </td><td style="text-align:right; "> * * * * * * * 8.50 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Server4</td><td style="text-align:right; ">5.00</td><td style="text-align:right; ">90.00%</td><td style="text-align:right; "> * * * * * * * * * *4.5 </td><td style="text-align:right; "> * * * * * * * * * 4.5 </td><td style="text-align:right; "> * * * * * * * *4.50 </td><td style="text-align:right; "> * * * 35.50 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 4.50 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Server5</td><td style="text-align:right; ">5.00</td><td style="text-align:right; ">90.00%</td><td style="text-align:right; "> * * * * * * * * * *4.5 </td><td style="text-align:right; "> * * * * * * * * * 4.5 </td><td style="text-align:right; "> * * * * * * * *4.50 </td><td style="text-align:right; "> * * * 31.00 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 4.50 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Server6</td><td style="text-align:right; ">5.00</td><td style="text-align:right; ">90.00%</td><td style="text-align:right; "> * * * * * * * * * *4.5 </td><td style="text-align:right; "> * * * * * * * * * 4.5 </td><td style="text-align:right; "> * * * * * * * *4.50 </td><td style="text-align:right; "> * * * 26.50 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 4.50 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Server7</td><td style="text-align:right; ">2.00</td><td style="text-align:right; ">60.00%</td><td style="text-align:right; "> * * * * * * * * * *1.2 </td><td style="text-align:right; "> * * * * * * * * * 1.2 </td><td style="text-align:right; "> * * * * * * * *1.20 </td><td style="text-align:right; "> * * * 25.30 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 1.20 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Server8</td><td style="text-align:right; ">2.00</td><td style="text-align:right; ">60.00%</td><td style="text-align:right; "> * * * * * * * * * *1.2 </td><td style="text-align:right; "> * * * * * * * * * 1.2 </td><td style="text-align:right; "> * * * * * * * *1.20 </td><td style="text-align:right; "> * * * 24.10 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 1.20 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Server9</td><td style="text-align:right; ">1.00</td><td style="text-align:right; ">60.00%</td><td style="text-align:right; "> * * * * * * * * * *0.6 </td><td style="text-align:right; "> * * * * * * * * * 0.6 </td><td style="text-align:right; "> * * * * * * * *0.60 </td><td style="text-align:right; "> * * * 23.50 </td><td style="text-align:right; "> * * * * * * * * * * - *</td><td style="text-align:right; "> * * * * * * * 0.60 </td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; "> * * * * * * * * *50.5 </td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; ">Total Load to be distributed</td><td style="text-align:right; ">60</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; ">Total Distributed</td><td style="text-align:right; "> * * * * * * * 50.50 </td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="********20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; ">Excess / (Undistributed)</td><td style="text-align:right; "> * * * * * * * *(9.50)</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G1</td><td >=E13</td></tr><tr><td >F2</td><td >=MIN(E2, G1)</td></tr><tr><td >G2</td><td >=G1-F2</td></tr><tr><td >H2</td><td >=MIN($D2-$E2, $G$10*<span style=' color:008000; '>(<span style=' color:#0000ff; '>(D2-E2)</span>/SUMPRODUCT<span style=' color:#0000ff; '>($D$2:$D$10-$E$2:$E$10)</span>)</span>)</td></tr><tr><td >I2</td><td >=F2+H2</td></tr><tr><td >D11</td><td >=SUM(D2:D10)</td></tr><tr><td >E14</td><td >=SUM(I2:I10)</td></tr><tr><td >E15</td><td >=E14-E13</td></tr></table></td></tr></table>

 

[Excruciating]

Awesome! Nearly cracked it, thanks soooo much! Couple of questions. What's the logic behind creating the 'optimal' calculation and not just using the recommended max? When I look at the returned values they don't seem to fit the real life scenario. We have a 20 unit max box with the same optimal load as a 10 unit max box? This calculates that bigger boxes would optimally run at 67% load (except the server one which is only using 33%). The other smaller boxes seem to run at their recommended max values. Shouldn't all of them run at their maximums? ie why not just compute the load up to the maximum?

 

[iliace]

It was just a guess. I think the real logic is up to you. You could change the SUMPRODUCT piece to use the max instead:

SUMPRODUCT($B$2:$B$10-$E$2:$E$10)

 

[Excruciating]

Ok, on my PC today and have been testing this. </br> With our figures, we've got a set of servers where we want to distribute a set of 'units'. Units are based on what is being pushed onto those boxes (eg bandwidth, transactions, work orders... whatever). This is our 'total load to be distributed' and is set to begin with. </br> We want to have enough boxes in that list to make sure the 'Total load to be distributed' fits nicely across the available servers. We don't want too many servers ie a lot of '(undistributed)', and we don't want too few servers so the 'total to be distributed' is greater than the sum('recommended' for each server). So, if there are 9 servers at the moment with 60 units to put across them. Available capacity = sum(recommended max load) = 50.5. We've got an excess of 9.5 so the model shows we need another server or two to handle that. </br> Model works. Great. </br> However, we have a bug..... Try taking the Load to be distributed down to something way smaller than total recommended load.... say to 10. At this point you would expect this to easily fit across the available servers, there to be only 10 units distributed, and lots of 'un-allocated' spare space. You could then drop out a lot of servers and stick with one big one. However with the calculations we still get 45 distributed, rather than a max of 10. </br> So, I think we need to drop optimal, this is redundant as we already have 'max recommended load' (which is the optimal for that server) </br> And need to work out why we're not using the 'units to be allocated' properly. We're using distribution rather than a proportion of the units perhaps? </br> Make sense? </br> </br> Excel 2013 64 bit

	A	B	C	D	E	F	G	H	I

<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead> </thead><tbody> [TD="align: center"]1[/TD] [TD="bgcolor: #FFFF00, align: center"]Server[/TD] [TD="bgcolor: #FFFF00, align: center"]Max Load[/TD] [TD="bgcolor: #FFFF00, align: center"]Threshold[/TD] [TD="bgcolor: #FFFF00, align: center"]Recommended[/TD] [TD="bgcolor: #FFFF00, align: center"]Optimal[/TD] [TD="bgcolor: #FFFF00, align: center"]Distribution[/TD] [TD="bgcolor: #FFFF00, align: center"]10[/TD] [TD="bgcolor: #FFFF00, align: center"]Suboptimal[/TD] [TD="bgcolor: #FFFF00, align: center"]Total Load[/TD] [TD="align: center"]2[/TD] [TD="bgcolor: #FFFFFF"]Server1[/TD] [TD="bgcolor: #FFFFFF, align: right"]20[/TD] [TD="bgcolor: #FFFFFF, align: right"]85%[/TD] [TD="bgcolor: #FFFFFF, align: right"]17.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="align: right"]3.3[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.9[/TD] [TD="bgcolor: #FFFFFF, align: right"]13.6[/TD] [TD="align: center"]3[/TD] [TD="bgcolor: #FFFFFF"]Server2[/TD] [TD="bgcolor: #FFFFFF, align: right"]10[/TD] [TD="bgcolor: #FFFFFF, align: right"]85%[/TD] [TD="bgcolor: #FFFFFF, align: right"]8.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="align: right"]3.3[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]7.9[/TD] [TD="align: center"]4[/TD] [TD="bgcolor: #FFFFFF"]Server3[/TD] [TD="bgcolor: #FFFFFF, align: right"]10[/TD] [TD="bgcolor: #FFFFFF, align: right"]85%[/TD] [TD="bgcolor: #FFFFFF, align: right"]8.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="bgcolor: #FFFFFF, align: right"]6.7[/TD] [TD="align: right"]3.3[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]7.9[/TD] [TD="align: center"]5[/TD] [TD="bgcolor: #FFFFFF"]Server4[/TD] [TD="bgcolor: #FFFFFF, align: right"]5[/TD] [TD="bgcolor: #FFFFFF, align: right"]90%[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: right"]5.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: center"]6[/TD] [TD="bgcolor: #FFFFFF"]Server5[/TD] [TD="bgcolor: #FFFFFF, align: right"]5[/TD] [TD="bgcolor: #FFFFFF, align: right"]90%[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: right"]5.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: center"]7[/TD] [TD="bgcolor: #FFFFFF"]Server6[/TD] [TD="bgcolor: #FFFFFF, align: right"]5[/TD] [TD="bgcolor: #FFFFFF, align: right"]90%[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: right"]5.5[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]4.5[/TD] [TD="align: center"]8[/TD] [TD="bgcolor: #FFFFFF"]Server7[/TD] [TD="bgcolor: #FFFFFF, align: right"]2[/TD] [TD="bgcolor: #FFFFFF, align: right"]60%[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="align: right"]8.8[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="align: center"]9[/TD] [TD="bgcolor: #FFFFFF"]Server8[/TD] [TD="bgcolor: #FFFFFF, align: right"]2[/TD] [TD="bgcolor: #FFFFFF, align: right"]60%[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="align: right"]8.8[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]1.2[/TD] [TD="align: center"]10[/TD] [TD="bgcolor: #FFFFFF"]Server9[/TD] [TD="bgcolor: #FFFFFF, align: right"]1[/TD] [TD="bgcolor: #FFFFFF, align: right"]60%[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.6[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.6[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.6[/TD] [TD="align: right"]9.4[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.0[/TD] [TD="bgcolor: #FFFFFF, align: right"]0.6[/TD] [TD="align: center"]11[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"]50.5[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="align: center"]12[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="align: center"]13[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"]Total Load to be distributed[/TD] [TD="bgcolor: #FFFFFF, align: right"]10[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="align: center"]14[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"]Total Distributed[/TD] [TD="bgcolor: #FFFFFF, align: right"]45.9[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="align: center"]15[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"]Excess / (Undistributed)[/TD] [TD="bgcolor: #FFFFFF, align: right"]35.9[/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] [TD="bgcolor: #FFFFFF, align: right"][/TD] </tbody>

Sheet1​

Worksheet Formulas <thead> [TH="******* 10px"]Cell[/TH] [TH="align: left"]Formula[/TH] </thead><tbody> [TH="******* 10px, bgcolor: #DAE7F5"]D2[/TH] [TD="align: left"]=B2*C2

[TH="******* 10px, bgcolor: #DAE7F5"]E2[/TH] [TD="align: left"]=MIN( D2, AVERAGE($B$2:INDEX(B:B, COUNTA(B:B))) )[/TD] [TH="******* 10px, bgcolor: #DAE7F5"]F2[/TH] [TD="align: left"]=MIN( E2, $G$1 )[/TD] [TH="******* 10px, bgcolor: #DAE7F5"]G2[/TH] [TD="align: left"]=$E$13-F2[/TD] [TH="******* 10px, bgcolor: #DAE7F5"]H2[/TH] [TD="align: left"]=MIN( $D2-$E2, $G$10*((D2-E2)/SUMPRODUCT($D$2:$D$10-$E$2:$E$10)) )[/TD]