Need Assistance With Percentage Formula

[jesse lee]

My formula is meant to calulate the number of device needed based on the capacity characteristics of the user. The device has 100 ports.
For Business users, the ratio is 80 users per port, for Residential users the ratio is 160 users per port.

I am using the following formula:
ROUNDUP(I5*H14/160/100,0)+ROUNDUP(I5*(1-H14)/80/100,0)

I5=Number of Subscribers
H14=Percentage of Residential users

The problem I have is that this formula does not take into consideration any capacity remaining from the first calulation, so if I have:
I5=9000 subscribers and
H14=1% residential
the actual total is 1.119735, however using the ROUNDUP formula above, I get a Sum of 3 instead of the next whole number which should be 2.

Any advise is greatly appreciated

 

[jpen]

If I undrstand it. Try the following formula

ROUNDUP( ((I5*H14)/160) + ((I5*(1-H14))/80) ; 0)

JH

 

[anubis]

Hi Jesse lee.

Maybe this?

=ROUNDUP((((I5*H4)/160)+((I5*(1-H4))/80))/100,0)

HTH

 

[jesse lee]

That did the trick - Thanks Dave!!!

Thanks