Extracting Data (Updated again example)

G

Gary Thomson

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 there was a simplified version of what
I need to solve for, which builds in 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??)?????
 
D

Dan E

Gary,

I posted a reply as well, so I'll continue with the formula's I
was working with as opposed to switching to Bernies.
Note: you sure do like multi and cross posting

For your first request

=SUMPRODUCT(((ISERROR(SEARCH(A13,$C$2:$L$9))=FALSE)
-(IF(Sheet2!$C$2:$L$9="x",1/2,0)))*($B$2:$B$9))
array entered (control + shift + enter)

where:
A13 would contain "a"
$C$2:$L$9 would contain your codes (a, b, abc, etc...)
Sheet2!$C$2:$L$9 would contain your x's
$B$2:$B$9 would contain your costs

As for your second request, that may take a little longer if
it's done at all.

Dan E


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??)?????
 
D

Dan E

Gary,

Got it this time, for your first complication

=SUMPRODUCT((ISERROR(SEARCH(A13,$C$2:$L$9))=FALSE)*(IF(Sheet2!$C$2:$L$9="x",1/2,1))*($B$2:$B$9))

Dan E

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??)?????
 
D

Dan E

Gary,

A 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:D17
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

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 there was a simplified version of what
I need to solve for, which builds in 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??)?????
 
G

Gary Thomson

Many THanks for these Dan!!!

I am currently putting them into my Spreadsheet - if I
have any problems you said I could post a request - how do
I do that?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting Data 1
If Function 1
Extracting Data (Updated) 1
lookup two different variables 0
Mail Merge Problem 1
Cell references 3
Finding cell references 0
Finding the weighted average of text that have been given values 1

Top