Desperately Seeking Calculation Assistance

[jesse lee]

I have a spreadsheet to calculate the number of physical servers required to support an application in a mixed business / residential environment. And can’t seem to make it work when I have a mixed percentage of Business and Residential users.
Facts :
1 – Node will support a maximum of 150,000 subscribers for Bus or Res.
2 – Each node has 1 standby server
3 – For Bus, each device will support 20,000 subs to a maximum of 8 servers per node +1 standby server.
4 – For Res, each server will support 40,000 subs to a maximum of 4 servers per node +1 standby server.

Where I am having trouble is when I have more than one node (I35>150000) in a mixed Bus / Res mode. My formulas are as follows:

I35 = Number of subscribers
I36 = Number of nodes required
F37 = Percentage of residential users

Business
=IF(AND(B37="Yes",C37="N+1",$I$35>150001),ROUNDUP((I35-(I36-1)*150000)/20000,0)+1+ROUNDUP((I36-1),0)*9,0)

Residential
=IF(AND(B37="Yes",C37="N+1",$I$35>150001),ROUNDUP((I35-(I36-1)*150000)/40000,0)+1+ROUNDUP((I36-1),0)*5,0)
Mixed
?????

I cannot seem to get a formula for calculating the number of servers required in a mixed business / residential environment say as defined in I37 residential.

Any assistance is greatly appreciated

 

[acw]

Hi

Just a starting point but how about

I36: =(I35*F37/40000+ROUNDUP(1*F37,0))+(I35*(1-F37)/20000+ROUNDUP((1-F37)*1,0))

This uses the number in F37 to workout the bus / res split (no need for another entry) and starts to assume that there will be a backup for each of the bus / res if both exist.


Tony

 

[jesse lee]

Thanks Tony....