Help for ICT Gcse c/w

Hi

Basically I am making a model for my ICT coursework (AQA), it's about dog
training courses for a vets surgery.

For part of it, we have to work out the minimum number of dogs needed for a
course to run (has to be less than �50).

I have made a table, which links to other worksheets..


      No. of dogs: Cost per course: Cost per dog: Can course run?
      1 �348.35 �348.35 No
      2 �350.60 �175.30 No
      3 �352.85 �117.62 No
      4 �355.10 �88.78 No
      5 �357.35 �71.47 No
      6 �359.60 �59.93 No
      7 �361.85 �51.69 No
      8 �364.10 �45.51 Yes
      9 �366.35 �40.71 Yes
      10 �368.60 �36.86 Yes
      11 �370.85 �33.71 Yes
      12 �373.10 �31.09 Yes
      13 �375.35 �28.87 Yes
      14 �377.60 �26.97 Yes
      15 �379.85 �25.32 Yes


Not sure if that table above will come out right!

Anyway, underneath, I want to write a sentence: The minimum number of dogs
needed is....... Is there anyway to fill that blank automatically according
to where the yes and no's start? These are going to change when different
venues are selected, or prices are changed. I just kinda need to read across
the first yes and insert that into the blank space.

Thanks


0
na3784 (3)
5/21/2004 11:05:04 PM
excel 39879 articles. 2 followers. Follow

9 Replies
592 Views

Similar Articles

[PageSpeed] 48

Assuming your sample table is in cols A to D,
data from row2 down and presumed sorted in
descending order by col C (Cost per dog) - as posted

Put in say, E1:

=OFFSET($D$1,MATCH("Yes",$D:$D,0)-1,COLUMN()-8)

Copy E1 across to G1

E1:G1 will return the first occurence of "Yes" in col D

E1 returns: 8 (No. of dogs)
G1 returns: �364.10 (Cost per course)
G1 returns: �45.51 (Cost per dog)

---
To include the text expressions for E1:G1, you could put in say:

E2: ="The minimum number of dogs needed is "&E1
E3: ="The cost per course is "&F1
E4: ="The cost per dog is "&G1

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
"NK" <na@nospam.com> wrote in message
news:AWvrc.3372$882.1162@front-1.news.blueyonder.co.uk...
> Hi
>
> Basically I am making a model for my ICT coursework (AQA), it's about dog
> training courses for a vets surgery.
>
> For part of it, we have to work out the minimum number of dogs needed for
a
> course to run (has to be less than �50).
>
> I have made a table, which links to other worksheets..
>
>
>       No. of dogs: Cost per course: Cost per dog: Can course run?
>       1 �348.35 �348.35 No
>       2 �350.60 �175.30 No
>       3 �352.85 �117.62 No
>       4 �355.10 �88.78 No
>       5 �357.35 �71.47 No
>       6 �359.60 �59.93 No
>       7 �361.85 �51.69 No
>       8 �364.10 �45.51 Yes
>       9 �366.35 �40.71 Yes
>       10 �368.60 �36.86 Yes
>       11 �370.85 �33.71 Yes
>       12 �373.10 �31.09 Yes
>       13 �375.35 �28.87 Yes
>       14 �377.60 �26.97 Yes
>       15 �379.85 �25.32 Yes
>
>
> Not sure if that table above will come out right!
>
> Anyway, underneath, I want to write a sentence: The minimum number of dogs
> needed is....... Is there anyway to fill that blank automatically
according
> to where the yes and no's start? These are going to change when different
> venues are selected, or prices are changed. I just kinda need to read
across
> the first yes and insert that into the blank space.
>
> Thanks
>
>


0
demechanik (4694)
5/22/2004 3:49:38 AM
Clarification .. :

> E1:G1 will return the first occurence of "Yes" in col D

should read as:

> E1:G1 will return the corresponding values of cols A to C
> for the first occurence of "Yes" in col D

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---


0
demechanik (4694)
5/22/2004 3:58:53 AM
sorry, typo in line :

> G1 returns: �364.10 (Cost per course)

should be:

> F1 returns: �364.10 (Cost per course)

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---


0
demechanik (4694)
5/22/2004 4:31:28 AM
One way:

use the formula:

="The minimum number of dogs needed is " &
INDEX(A2:A16,MATCH("yes",D2:D16,0))

assumes the number of dogs is in the range A2 to A16 and your yes/no formula
is in D2 to D16

Regards

Trevor


"NK" <na@nospam.com> wrote in message
news:AWvrc.3372$882.1162@front-1.news.blueyonder.co.uk...
> Hi
>
> Basically I am making a model for my ICT coursework (AQA), it's about dog
> training courses for a vets surgery.
>
> For part of it, we have to work out the minimum number of dogs needed for
a
> course to run (has to be less than �50).
>
> I have made a table, which links to other worksheets..
>
>
>       No. of dogs: Cost per course: Cost per dog: Can course run?
>       1 �348.35 �348.35 No
>       2 �350.60 �175.30 No
>       3 �352.85 �117.62 No
>       4 �355.10 �88.78 No
>       5 �357.35 �71.47 No
>       6 �359.60 �59.93 No
>       7 �361.85 �51.69 No
>       8 �364.10 �45.51 Yes
>       9 �366.35 �40.71 Yes
>       10 �368.60 �36.86 Yes
>       11 �370.85 �33.71 Yes
>       12 �373.10 �31.09 Yes
>       13 �375.35 �28.87 Yes
>       14 �377.60 �26.97 Yes
>       15 �379.85 �25.32 Yes
>
>
> Not sure if that table above will come out right!
>
> Anyway, underneath, I want to write a sentence: The minimum number of dogs
> needed is....... Is there anyway to fill that blank automatically
according
> to where the yes and no's start? These are going to change when different
> venues are selected, or prices are changed. I just kinda need to read
across
> the first yes and insert that into the blank space.
>
> Thanks
>
>


0
Trevor9259 (673)
5/22/2004 10:32:38 AM
Thanks SO much!

My data started in A, not A2, but the forumla still worked fine.

Great help, much appreciated.

"Max" <demechanik@yahoo.com> wrote in message
news:OcO50W7PEHA.3732@TK2MSFTNGP11.phx.gbl...
> sorry, typo in line :
>
> > G1 returns: �364.10 (Cost per course)
>
> should be:
>
> > F1 returns: �364.10 (Cost per course)
>
> --
> Rgds
> Max
> xl 97
> --
> Please respond, in newsgroup
> xdemechanik <at>yahoo<dot>com
> ---
>
>


0
na3784 (3)
5/22/2004 10:36:17 AM
Oh also, hw would I adjust the formula so I can move it from E1, F1 and G1
to cells underneath the table?

That way I can give it headings to make it more clearer.


"Max" <demechanik@yahoo.com> wrote in message
news:OcO50W7PEHA.3732@TK2MSFTNGP11.phx.gbl...
> sorry, typo in line :
>
> > G1 returns: �364.10 (Cost per course)
>
> should be:
>
> > F1 returns: �364.10 (Cost per course)
>
> --
> Rgds
> Max
> xl 97
> --
> Please respond, in newsgroup
> xdemechanik <at>yahoo<dot>com
> ---
>
>


0
na3784 (3)
5/22/2004 10:39:15 AM
Maybe use these instead ..
(with the columns arg of OFFSET converted to "hard" numbers)

E1: =OFFSET($D$1,MATCH("Yes",$D:$D,0)-1,-3)
F1:=OFFSET($D$1,MATCH("Yes",$D:$D,0)-1,-2)
G1:=OFFSET($D$1,MATCH("Yes",$D:$D,0)-1,-1)

You can now move these to the cells below

Column() is used as an incrementer for easy copy across from E1 to G1
It simply returns the number of the column (col A = column # 1, and so on)
e.g.: "Column()" in col E returns 5, in col F it returns 6, etc

Try experiment by adjusting the part " .. column()-8" to suit
the column where you want to place the formulas
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
"NK" <na@nospam.com> wrote in message
news:o5Grc.7420$FV7.4565@doctor.cableinet.net...
> Oh also, hw would I adjust the formula so I can move it from E1, F1 and G1
> to cells underneath the table?
>
> That way I can give it headings to make it more clearer.



0
demechanik (4694)
5/22/2004 11:03:40 AM
You're welcome !
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
"NK" <na@nospam.com> wrote in message
news:o5Grc.7419$FV7.135@doctor.cableinet.net...
> Thanks SO much!
>
> My data started in A, not A2, but the forumla still worked fine.
>
> Great help, much appreciated.



0
demechanik (4694)
5/22/2004 11:04:32 AM
or, to be able to move the formula to any cell:

="The minimum number of dogs needed is " &
INDEX($A$2:$A$16,MATCH("yes",$D$2:$D$16,0))

Regards

Trevor


"Trevor Shuttleworth" <Trevor@Shucks.demon.co.uk> wrote in message
news:uHIblg#PEHA.1340@TK2MSFTNGP12.phx.gbl...
> One way:
>
> use the formula:
>
> ="The minimum number of dogs needed is " &
> INDEX(A2:A16,MATCH("yes",D2:D16,0))
>
> assumes the number of dogs is in the range A2 to A16 and your yes/no
formula
> is in D2 to D16
>
> Regards
>
> Trevor
>
>
> "NK" <na@nospam.com> wrote in message
> news:AWvrc.3372$882.1162@front-1.news.blueyonder.co.uk...
> > Hi
> >
> > Basically I am making a model for my ICT coursework (AQA), it's about
dog
> > training courses for a vets surgery.
> >
> > For part of it, we have to work out the minimum number of dogs needed
for
> a
> > course to run (has to be less than �50).
> >
> > I have made a table, which links to other worksheets..
> >
> >
> >       No. of dogs: Cost per course: Cost per dog: Can course run?
> >       1 �348.35 �348.35 No
> >       2 �350.60 �175.30 No
> >       3 �352.85 �117.62 No
> >       4 �355.10 �88.78 No
> >       5 �357.35 �71.47 No
> >       6 �359.60 �59.93 No
> >       7 �361.85 �51.69 No
> >       8 �364.10 �45.51 Yes
> >       9 �366.35 �40.71 Yes
> >       10 �368.60 �36.86 Yes
> >       11 �370.85 �33.71 Yes
> >       12 �373.10 �31.09 Yes
> >       13 �375.35 �28.87 Yes
> >       14 �377.60 �26.97 Yes
> >       15 �379.85 �25.32 Yes
> >
> >
> > Not sure if that table above will come out right!
> >
> > Anyway, underneath, I want to write a sentence: The minimum number of
dogs
> > needed is....... Is there anyway to fill that blank automatically
> according
> > to where the yes and no's start? These are going to change when
different
> > venues are selected, or prices are changed. I just kinda need to read
> across
> > the first yes and insert that into the blank space.
> >
> > Thanks
> >
> >
>
>


0
Trevor9259 (673)
5/22/2004 8:55:17 PM
Reply:

Similar Artilces:

Missing Report Manager help file
Greetings: I run Windows XP home and Office XP Professional. Sometime last year I installed Report Manager add-in for Excel. In March 2005 I had to do a reinstall. Since then, when I click on View>Report>Manager>help I get a message stating that the XLMAIN8.HLP file cannot be found. The utility is checked in the Add-On dialog box. As I still have the download in the My Downloads folder I have even tried reinstalling the utility again but still the help file remains lost somewhere. Can someone offer some advice on what to do next? Where could the file have gone? How can I get it back...

help sos #2
error 421 smtp service is not available ...

W-2c
Does GP Dynamics not have any options to create a W-2c. The state ID number is incorrect on the W-2s. How am I supposed to do a large amount of W-2cs? Hopefully not by hand, any ideas? ...

Help!! think I messed this up!!
Hey need your help!! We have 2 domains, 1 2000 and 1 4.0 domain. We have 2 2000 exchange servers running in the 2000 domain and 1 exchange 5.5 box running in the 4.0 domain. We have ADC running. I moved the 5.5 user to the 2k exchange box like your supposed to. Management doesn't want to move the user into the 2000 domain. Nice huh? Well they could open their mailbox on the first 2000 exchange server. But it would act like it was trying to connect to the 2nd 2000 exhange box becuase it was coming up with a logon box with the 2nd exchange server name. So I disabled the ADC and mov...

screen is showing slides in backward order .. . help!
Hello I have a pptx presentation with six slides. For some reason, everything is defualting the the right (the bullets come out to the right of the words, the slides print as handouts in backwards order, the outline pane is on the right hand side of the slide pane). THis is only happening for this presentation. Others I create are the typical way PP usually works. PLEASE HELP! I wish I could upload it here but am unable to do so. See your other post -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_t...

problem with calling mfc dll from #C
Hi All, Debug Assertion Failed File dbgheap.c _CrtIsValidHeapPointer() I get the above while trying to CGAL::Intersection(Polygon_2 , Polygon_2,std::back_inserter(...)) in a MFC DLL project (Visual Studio .Net 2003) I call it from C# project. What to do to fix my project? Please Help Thanks Miki How are you calling the MFC dll? Is it managed code with MFC or simply an COM server which has been imported in managed code? Also, you are better off posting this in dotnet.vc newsgroup. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "miki" <mich...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

Create Folders and Write To Hard Disk Other than C:
My app can generate many gigabytes of data files (several megabytes each) per the user choice. For those who wonder, this happens over many hours and or days. I am making XP and up a requirement for using the app. So, for XP and up how do I do the following: (1)Set permissions on a different hard drive (other than C:) to read, write and delete data files. (a)for this user (b)for all users (common data). (2)Create a folder (assumes this is different than "normal"). I may be totally wrong, but I assume that reading, writing and deleting the file in this fo...

"The Disk is full trying to write to c:\..."
Help I need to use a file and I have it on a disk. I open it up and try to save it on my hard drive but I get the error message "the disk is full trying to write to c:\..." My hard drive is 3/4 empty and there is no way it is full. What can I do? I need this file immediately and don't know what to do. The file also won't let me overwrite it on the disk saying the disk is write-protected but it is not. Thanks for the help. Nancy Don't open it on the disk. Copy the file over to your hard disk, make sure it is not a read-only file, then open it on your system. -- J...

inbox.dbx infected with netsky !!! HELP!!!
I have one windows xp pro laptop with outlook express the inbox.dbx file seems corrupted because months of email just suddenly went missing for no reason I suspected that it is corrupted I installed easyrecovery pro and ran the emailrepair function to recover the inbox.dbx file I successfully recovered most of the mails But when the easyrecovery emailrepair is just about to write the repaired inbox.dbx file into the hard drive, my symantec antivirus corporate edition complains that the repaired inbox.dbx file is infected with the NETSKY virus .... and deletes the inbox.dbx file because it...

Pie Chart Help
Can anyone help? I have a spreadsheet to which I have one column that has different letters in, I need to create a pie chart that shows the amount of the same letter (i.e. 'P's in blue, 'F's in red etc) just so I can show how many of one letter there is? Any ideas and please explain fully as I am quite a new user Add a heading to the column of letters then you could create a pivot table to summarize the data, and create a pie chart from the pivot table results. There are instructions for pivot tables in Excel's Help, and Jon Peltier has information and links: ht...

help with cell referencing in named ranges
I have a named range covering about 30 columns of data. I'd like to lookup within the named range based on values in the 1st column, and a specific date which is the label on top of each of the other 29 columns. I have typed in a new column number as necessary, but I'd like to automate the column number by looking at a separate cell which gives the date. Ex: Dates across the top of the named range are 1/1/90, 1/1/91, etc thru 1/1/2003(in sheet1). Lets say I have a cell in sheet2(d1)that has the date 1/1/2003. In that same sheet starting in cell a1 I have a value which ...

HELP need index of the actual selected point
Hi, I need help. I must get the index of a the currently selectd point in the chart. I can't use the Mouseup event. It will be better to determinate the x and y value from the actual selected point but the index will also be good. Please help! thx Jens You need to use an old-style XLM command for this, as Stephen Bullen pointed out in his post 14-Jan-2001: Sub WhichPoint() Dim sPoint As String Dim iSeries As Integer, iPoint As Integer sPoint = ExecuteExcel4Macro("SELECTION()") If sPoint Like "S*P*" Then iSeries = Val(Mid$(sPoint, 2...

POP email multiple duplicate downloads HELP!
Hi, ISP is SBC Global. I just installed Outlook 2003 yesterday and it keeps downloading ALL the mail I have in the SBC web inbox, even though I"ve marked them as "read" online, multiple times. Duplicate finder [bought and installed] doesn't recognize most of them as dupes. I've done "detect & repair," [got error 1311 source file not found; C:\msocache\allusers\90000409-60000-11D3-8CFE-0150048383C9\ZS561.CAB. Couldn't find that in my installation disk, either.] SBC says they do not support Outlook 2003 -- "it's too new." Superviso...

Enter date in one cell, and the week ending date [Sunday] appears in the "W/E" cell
I have an XL 2003 workbook that I enter the date an event occured in cell C7. I want to make another cell [C2]display the week ending date [Sunday], based ono the date entered in cell C7. How can I accomplish this? Thanks, Tonso =7-WEEKDAY(C7,2)+C7 Daniel > I have an XL 2003 workbook that I enter the date an event occured in > cell C7. I want to make another cell [C2]display the week ending date > [Sunday], based ono the date entered in cell C7. How can I accomplish > this? > > Thanks, > > Tonso hi, =IF(WEEKDAY(C7)=1,C7,C7+(8-WEEKDAY(C7))) -- isabelle On ...

Help with System Attendant
Hi all: My situation is this. We had a single system running AD/exchange (windows and exchange 2000). Not ideal, I know :) The systems' mft became corrupted, and I spent a long night bringing up a 2nd exchange server. I was able to bring up the old server, move mailboxes, and have everyone up and running before the next biz day. I've since installed an AD controller on another machine, as the original (problem) server could die any day. I found where to set the 2nd server as a global catalog server, and thought I might have been done. That is, until I tried adding new use...

find&select button,help please?
Hi everybody, i'm using excel 2007,when i press the find&select button it dosen't sow me the cell i'm looking for,it has been disabled,although when i look the rows one by one i can find it,how can i able the find&select. i'd appreciate if u'd let me know,cuz i'm in trouble here ...

Format custom number: C-0000-L
Hi, I need to format a few cells in Excel to look like C-0000-L where "0000" represents four numbers and "L" represents a letter. I tried to do it in format - custom with C-0000-@ but it gives me an error message. I also tried to do it using the concatenate function but it doesn`t work. Can anyone please help me? Thanks! -- Sonia Custom format "C-"0000"-L" -- David Biddulph "Sonia" <Sonia@discussions.microsoft.com> wrote in message news:8EAB02FD-1AA5-4E80-880A-7C9A33A8FE01@microsoft.com... > Hi, I need to format...

Help with rules not working #2
Hi: I'm continuing to experience problems with Rules working. This time, I have Outlook 2003 connected to Exchange 2000 Server, set up to run on a PC (Win XP pro). I'm connected to that PC with a remote desktop session that is always open. None of the rules work unless I run them manually (even rules that are server side). Does anyone know if a command line switch (or anything else) will work to ensure that rules actually run automatically? Thanks for any suggestions -- Dab Cut off: yourhead to respond ...

Help] How to merge two charts?
1. There are two charts M and N. Column A of chart M means part-number (numbers and letters), Column B of chart M means part-name (in Japanese); Column A of chart N means part-number (numbers and letters), Column B of chart N means part-name (in English); 2. Column M contains much more parts than Column N, which means N belongs to M; But M and N obey exactly the same part-number naming rule; 3. While most people can't Japanese but English, so it is planned to add the English part-names from Column N into Column M, just as remarks to corresponding Japanese part-names, meaning ...

please help OE & OL between 2 users
I am admin account and run Outlook. Girlfriend is limited account and runs OE. When she right clicks pics and the like to send to mail recepient, it opens an Outlook new message window. Now, these messages never get sent as she doesnt use Outlook as her email program. They simply accumulate in the Outlook outbox and never get sent. I cant set OE as her default email as it wont let me do that (I guess since Im admin and I use it). What should be done? Ive tried this all but the registry change http://support.microsoft.com/?kbid=306098 and it wont take. Whats the best way to do...

Oy, I need some help!!
Hi, Here is the problem: I use outlook express my wife uses outlook 2000 on a separate computer. I tried to combine the 2 computers into one. I copied my contacts from OE, went to the other computer and imported them in OE. Everything looked fine. When I went to Outlook, all the contacts were the ones in OE. I don't know what happened to her contacts. Is there any way to find and recover them? Do I need specific data recovery software? Is there any available on line? Please help... she is gonna kill me...... then divorce me!!!! Usually if Outlook and Outlook Express are set to s...

Help with memory leak (possibly in CArray)
Hi all, I've an App that takes about 4 hours to run before it throws the error I'm trying to fix, so I don't really fancy single-stepping it. It's throwing me a user break point in _heap_alloc_dbg at if (lRequest == _crtBreakAlloc) _CrtDbgBreak(); I guess this is an "out of heap memory" error. The App basically builds lots of models, tests them and then kicks the results out to a text file, so I'm creating and destroying a lot of objects, which is why I think I've a memory leak. The break actually comes during a call to CArray::SetSize, although I do apprec...

outlook express 5 help
I have a windows 2000 system that crashed. i backed up the outlook express folder and the applications data folder. I reinstalled Win2k. How do I import all the old Outlook express messages and address book on the new installation? Any help will be greatly appreciated. ...

How do I define column C so C(n) =A(n)+B(n)
I want to create a spreadsheet where some cell entries are mathematical functions of data in corresponding cells (same row) of other columns. For example, assume fixed data in columns A and B. In column C, I want C(1) to = A(1) + B(1) etc. Hi in C1 type =A1+B1 then move your cursor over the bottom right hand corner until you see a + (known as the autofill handle) hold the left mouse down and drag down the column as far as you need to go and let go, the formula will be filled in for you for each line Cheers julieD "S M Raucher" <S M Raucher@discussions.microsoft.com>...