Minimum? Formula

  • Thread starter Thread starter Gary Thomson
  • Start date Start date
G

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.
 
One way:


A1: <# of pupils>

Adults:

A2: =CEILING(A1/10,1)

Teachers:

A3: =CEILING(A2/A4,1)

Nurses:

A4: =A2 - A3
 
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
 
Back
Top