This is further to a post I made earlier today (posted on
Oct 22 2003 6:57AM), for which a solution was made (and
works! I was amazed at it, and still can't understand how
it calculates them, but I am happy it does!!)

The example I gave was a simplified version of what
I need to solve for, which builds on the following:

Suppose I have the following set-up:

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


In the example I gave in my earlier post, I wanted to add
up the amount that is attributed to "a" i.e:
£10 + £11 + £11 + £8 + £8 + £6 + £12 = £66.

Similarly, the total amount attributed to "b" i.e:

£11 + £11 + £8 + £8 + £8 = £46.

"c": £8 + £8 = £16.

"d": £8.

"e": £8.

and I asked how to do this, and got the answer, which was

=SUM(IF(ISERROR(FIND(B11,$C$2:$F$9)),0,$B$2:$B$9))


However, there are two complications on top of this:



First Complication:

In a seperate sheet there is the following data, for the
Usage of Each classroom:

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

Now, if a room/date combination has a "x" beside it ("x"
cannot appear in the first table above), the amount
attributed to "a", "b", "c" etc in that particular
room/date is halved.

i.e. C2 has Maths on 1-Feb at £10 in "UNAVAILABILITY"
table, but because it has an "x" in C2 in the "USAGE"
table, the actual amount attributed to "a" on 1-Feb in
Maths is £5, not £10.

But C3 has English on 1st Feb at £11 in "UNAVAILABILITY"
table, but since there is no "x" in C3 of the "USAGE"
table, the amount attributed to "a" on 1-Feb in English
remains at £11.



Second Complication:

If any letter (i.e. "a", "b", "c", etc (except "x")) has
appeared for five consecutive days, on the 6th day and
thereafter, the amount attributed to that letter is
increased by 50%. This is not shown in my examples, but
hopefully I have described it well enough to understand.





Is there a way of building both of these complications
into a formula as well (or at the very least one of
them??)?????
..