G
Gary Thomson
DanE, you answered my second complication, which works
based on what I said:
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.
You gave the following answer, which took account of both
complications:
solution to the second issue.
This solution requires you to add 3 empty columns
preceding your data
YOUR DATA (comma delimited)
A,B,C,D,E,F,G,H,I,J,K,L,
,,,Unit,Amount,01-Feb-03,02-Feb-03,03-Feb-03,04-Feb-03,05-
Feb-03,06-Feb-03,07-Feb-03,
,,,Math,10,a,,,,,,,,,
,,,English,11,a,ab,b,,,,
,,,Music,8,abc,ab,abcde,a,a,a,a
,,,P.E.,9,b,b,b,b,b,b,
,,,Geography,6,a,,,,,,
,,,History,6,,,,,,,
,,,Drama,7,,,,,,,
,,,Science,12,a,,,,,,
So your code to be found will be in D13
17
Codes being searched F2:O9
Amounts E2:E9
X range Sheet2!$F$2:$O$9
Formula
=SUMPRODUCT((ISERROR(SEARCH(D13,$F$2:$O$9))=FALSE)*(IF
(Sheet2!$F$2:$O$9="x",1/2,1))*($E$2:$E$9))+SUMPRODUCT
(((ISERROR(SEARCH(D13,$F$
2:$O$9))=FALSE)*(ISERROR(SEARCH(D13,OFFSET($F$2:$O$9,0,-
1)))=FALSE)*(ISERROR(SEARCH(D13,OFFSET($F$2:$O$9,0,-2)))
=FALSE)*(ISERROR(SEA
RCH(D13,OFFSET($F$2:$O$9,0,-3)))=FALSE)*(ISERROR(SEARCH
(D13,OFFSET($F$2:$O$9,0,-4)))=FALSE)*(ISERROR(SEARCH
(D13,OFFSET($F$2:$O$9,0,-
5)))=FALSE)*0.5)*$E$2:$E$9)
If you would like you can post a request and I can send
you my sheet.
Dan E
However, for the second complication, the amount
attributed to a letter is only increased by 50% if there
is no "x" in the corresponding Unit/Date in the USAGE
table - if there is, no 50% addition applies.
Can this be solved?
based on what I said:
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.
You gave the following answer, which took account of both
complications:
solution to the second issue.
This solution requires you to add 3 empty columns
preceding your data
YOUR DATA (comma delimited)
A,B,C,D,E,F,G,H,I,J,K,L,
,,,Unit,Amount,01-Feb-03,02-Feb-03,03-Feb-03,04-Feb-03,05-
Feb-03,06-Feb-03,07-Feb-03,
,,,Math,10,a,,,,,,,,,
,,,English,11,a,ab,b,,,,
,,,Music,8,abc,ab,abcde,a,a,a,a
,,,P.E.,9,b,b,b,b,b,b,
,,,Geography,6,a,,,,,,
,,,History,6,,,,,,,
,,,Drama,7,,,,,,,
,,,Science,12,a,,,,,,
So your code to be found will be in D13
17Codes being searched F2:O9
Amounts E2:E9
X range Sheet2!$F$2:$O$9
Formula
=SUMPRODUCT((ISERROR(SEARCH(D13,$F$2:$O$9))=FALSE)*(IF
(Sheet2!$F$2:$O$9="x",1/2,1))*($E$2:$E$9))+SUMPRODUCT
(((ISERROR(SEARCH(D13,$F$
2:$O$9))=FALSE)*(ISERROR(SEARCH(D13,OFFSET($F$2:$O$9,0,-
1)))=FALSE)*(ISERROR(SEARCH(D13,OFFSET($F$2:$O$9,0,-2)))
=FALSE)*(ISERROR(SEA
RCH(D13,OFFSET($F$2:$O$9,0,-3)))=FALSE)*(ISERROR(SEARCH
(D13,OFFSET($F$2:$O$9,0,-4)))=FALSE)*(ISERROR(SEARCH
(D13,OFFSET($F$2:$O$9,0,-
5)))=FALSE)*0.5)*$E$2:$E$9)
If you would like you can post a request and I can send
you my sheet.
Dan E
However, for the second complication, the amount
attributed to a letter is only increased by 50% if there
is no "x" in the corresponding Unit/Date in the USAGE
table - if there is, no 50% addition applies.
Can this be solved?