Extracting Data
 

I have already posted this in the "Worksheet Functions"
Forum, but I thought that perhaps it could be solved using
some sort of for...from...do loop?


I have the following set up in Excel (a list of Units in
Column A, and a list of days of the month in Row 1):

A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths
3 English
4 Music
5 P.E.
6 Geography
7 History
8 Drama
9 Science
.. .
.. .
.. .

These are all Classrooms in a School. On any given day, a
room can be Available or Unavailable.

If a room is Available, that means the pupils are able to
be taught in it, and there is no entry placed in it's
corresponding cell (e.g. suppose that the Maths room is
Available on the 3rd Feb, then cell D2 would be blank).

If a room in Unavailable, the pupils could not be taught
in the room and the firm that are running the school are
penalised as a result. The type of fault that caused the
Unavailability of the room is entered into the
corresponding cell (e.g. suppose the Science room was
Unavailable on the 1st Feb due to a burst water pipe
(which is labelled as FAULT "a"), then we would enter "a"
into cell B9). Thus the above array would perhaps look as
follows:


UNAVAILABILITY
A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths a a
3 English a ab ab
4 Music abc b abcde
5 P.E.
6 Geography
7 History
8 Drama
9 Science a
.. .
.. .
.. .

So in the above, Fault "a" has caused Unavailability in
the Maths Classroom on 1st & 2nd Feb, in the English
Classroom on 1st, 2nd & 3rd Feb, in the Music Classroom on
1st & 3rd Feb, and in the Science Classroom on 9th Feb.

Similarly, Fault "b" has caused Unavailability in the
English Classroom on 2nd & 3rd Feb, and in the Music
Classroom on 1st, 2nd & 3rd Feb, and so on.

All of the above would be included in a worksheet
called "UNAVAILABILITY".

Now, if a room is Unavailable on a given day, a penalty
applies, dependant on the size of the room. For example,
if the Maths Classroom is Unavailable, then the penalty is
£12 per day (no matter what type of fault caused the
Unavailability). Similarly, if the History Classroom is
Unavailable, the Deduction is £8 (again, no matter what
type of fault caused the Unavailability), since this room
is slightly smaller than the Maths Classroom. These
values could be stored in a separate column, say column AZ.

The problem I have is that within this set-up there is a
clause which states that "The minimum aggregate deduction
for a fault is £50". Thus for fault "a", if the total of
all the deductions applying for this fault is less than
£50, then we reset the total to £50. If the total is
already more than £50, it remains at that level.

So what I need to do is pull out all the deductions that
relate to fault "a", and check whether the total of these
is less than £50. Similarly, I need to do this for all
the deductions for fault "b", and so on. How can Excel do
this?

My apologies for the extremely long post - I felt it was
necessary so as anyone reading this could fully understand
what I meant!