Need assistance with a percentage formula

[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 (an understatement) is when I have more than one node 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",E37="Yes",I35<150001),ROUNDUP(I35/40000,0)+1,0)

I cannot seem to get my formula for calculating the number of servers required in a mixed environment, 50% residential. <banging head here>

For less than one node I am using this formula,
=IF(AND(B37="Yes",C37="N+1",E37="Mixed",I35<150001),ROUNDUP((((I35*F37)/200/200)+((I35*(1-F37))/100/200)),0)+1,0)
But I cannot get it to work correctly for the multiple nodes formula

Any help will be Greatly Appreciated

Thanks in advance,

Jesse

 

[NdNoviceHlp]

"(1-F37))/100/200" seems wrong. Try this for any number. HTH. Dave
=IF(AND(B37="Yes",C37="N+1",E37="Mixed",I35<150001),ROUNDUP((((I35*F37)/200/200)+((I35*(F37/100))/200)),0)+1,0)

 

[jesse lee]

The (1-F37)/100/200 is to determine the percentage of users that are residential.
So if the number of Subscribers (I35) is 400,000 and if F37 is 60%. I have to calculate both the number of residential users: I35*F37 which returns 240,000.
Then I have to calculate the remaining percentage for Business:
I35*(1-F37) which returns 260,000.

What I need help with is calculating the mixed deployments for multi-nodes
Bus:
=ROUNDUP((I35-(I36-1)*150000)/20000,0)+1+ROUNDUP((I36-1),0)*9,0)
Res:
=ROUNDUP((I35-(I36-1)*150000)/40000,0)+1+ROUNDUP((I36-1),0)*5,0)
Mixed:
=????

Thanks...

Jesse