Help for ICT Gcse c/w

  • Thread starter Thread starter NK
  • Start date Start date
N

NK

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
 
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
 
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
 
Thanks SO much!

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

Great help, much appreciated.
 
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.
 
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
 
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
 
Back
Top