Lookup function?

[Spursgirl]

I'm not sure if I'm using the right function, but I have a row of test
results and some cells have the option for a "pass/fail" result (not a number
or a grade).

I want to summarise the results from each section of the test, so am adding
the scores quite happily. However, if one of the questions is failed, this
needs to be shown in the summary.

I have tried to use Lookup but it won't display "Fail" in a cell on the
summary.

Any help would be greatly appreciated.

Thanks

 

[Simon Lloyd]

I'm not sure if I'm using the right function, but I have a row of test
results and some cells have the option for a "pass/fail" result (not a
number
or a grade).

I want to summarise the results from each section of the test, so am
adding
the scores quite happily. However, if one of the questions is failed,
this
needs to be shown in the summary.

I have tried to use Lookup but it won't display "Fail" in a cell on the
summary.

Any help would be greatly appreciated.

Thanks

What do you want to show, 1)the sum of the scores that didn't fail?,
2)the sum of the scores that failed? or 3)show a Pass or Fail depending
on the value of the sum?

If you want to do No.1 then use sumproduct like this (assuming your
scores are in column C and Pass or Fail in column D)
=SUMPRODUCT(--(D120="Pass")*(C1:C20)) or
=SUMIF(D120,"Pass",C1:C20)

If you want to do No.2 then use sumproduct like this (assuming your
scores are in column C and Pass or Fail in column D)
=SUMPRODUCT(--(D120="Fail")*(C1:C20)) or
=SUMIF(D120,"Pass",C1:C20)

If you want to do No.3 then use sumproduct like this (assuming your
scores are in column C and Pass or Fail in column D)
=IF(SUM(C1:C20)<=30,"Fail","Pass")

Or you can count the number of fails against the number of passes like
this:
=IF(COUNTIF(D120,"Pass")>COUNTIF(D120,"Fail"),"Pass","Fail")


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Spursgirl]

Hi Simon
Many thanks for your reply, which taught me some stuff I didn't know, but
didn't answer my question, because I obviously phrased it badly!!

The cells to which I was referring do not have a number in them; they have
the word "Pass" or "Fail". I wanted to be able to link that result to a
summary spreadsheet, so that I could enter "Pass" or "Fail" in each page of
the workbook and then report the question which this related to on the
summary sheet.

Example of summary sheet:-

Section A Section B Questions Failed

25 30 A3, B7


This is because there are certain questions which require a pass, otherwise
it is an automatic fail, regardless of the other scores.

I do not know if it is possible to do this automatically, rather than having
to check each page of the workbook and then keying the information in.

I really appreciate your response, and would be grateful for help on this
from you (or anyone else).

Thanks

 

[Simon Lloyd]

Hi Simon
Many thanks for your reply, which taught me some stuff I didn't know,
but
didn't answer my question, because I obviously phrased it badly!!

The cells to which I was referring do not have a number in them; they
have
the word "Pass" or "Fail". I wanted to be able to link that result to a
summary spreadsheet, so that I could enter "Pass" or "Fail" in each
page of
the workbook and then report the question which this related to on the
summary sheet.

Example of summary sheet:-

Section A Section B Questions Failed

25 30 A3, B7


This is because there are certain questions which require a pass,
otherwise
it is an automatic fail, regardless of the other scores.

I do not know if it is possible to do this automatically, rather than
having
to check each page of the workbook and then keying the information in.

I really appreciate your response, and would be grateful for help on
this
from you (or anyone else).

Thanks

Providing a workbook will better illustrate your problem, usually when
we can see your data (-it can be dummy data but must be of the same
type-) and your structure it is far easier for us to give you a
tailored, workable answer to your query

For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachmnets to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)