Desperately Seeking Calculation Assistance

jesse lee

New Member
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

=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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

acw

MrExcel MVP
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

Thanks Tony....

Replies
13
Views
3K
Replies
4
Views
1K
Replies
2
Views
431
Replies
3
Views
430

1,195,623
Messages
6,010,749
Members
441,567
Latest member
Flitbee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back