Minimum? Formula

[Gary Thomson]

Hi,

Long post this one!!

In cell A1 I enter the number of pupils in a nursery.

In cells A2 and A3, I want (respectively) the number of
teachers and nursery nurses required such that:

(i) The adult (i.e. teachers + nursery nurses) to pupil
ratio must be at least 1:10 with a minimum of at least 1
teacher;

(ii) The ratio of teachers to nursery nurses cannot fall
below 1 teacher to every 3 nursery nurses.

Having just the pupil numbers as my input, how do I get
the no. of teachers and nursery nurses to fall out?

Example Data:

20 pupils ->

1 teacher and 1 nursery nurse.

30 pupils ->

1 teacher and 2 nursery nurses (we could have "2 teachers
and 1 nursery nurse", or even "3 teachers and 0 nursery
nurses", but since teachers get paid more, we want ot be
as cost efficient as possible and so do not use either of
these options) (do I need another constraint here?)

40 pupils ->

1 teachers and 3 nursery nurses

50 pupils ->

2 teachers and 3 nursery nurses (we cannot have 1 teacher
and 4 nursery nurses, since this breaks the 1:3 ratio).

Many Thanks for your help,

Gary.

 

[JE McGimpsey]

One way:


A1: <# of pupils>

Adults:

A2: =CEILING(A1/10,1)

Teachers:

A3: =CEILING(A2/A4,1)

Nurses:

A4: =A2 - A3

 

[JE McGimpsey]

Oops - typo:

A3: =CEILING(A2/4,1)

 

[JulieD]

Hi

not sure if the OP is having the same problem as i am, but i'm getting a
circular reference error with the formula.

Cheers
JulieD

 

[Gary Thomson]

Hi, thanks for the answer, but I'm getting a circular ref.

 

[Gary Thomson.]

Yes, that works. THan you very much

Gary.

-----Original Message-----
Oops - typo:

A3: =CEILING(A2/4,1)

 

[JE McGimpsey]

There was a typo. See my followup post.