Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 6th 09, 08:24 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Creating a pass of fail condition


Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JH


--
jhumphreys
------------------------------------------------------------------------
jhumphreys's Profile: http://www.thecodecage.com/forumz/member.php?userid=470
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112852


  #2   Report Post  
Old July 6th 09, 09:22 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 245
Default Creating a pass of fail condition

How do you assess pass or fail for each?
If you do it by number:

=IF(COUNTIF(A1:A5,"74")=5,SUM(A1:A5),0)

requires each number in A1:A5 be greater than 74 or it returns 0.

If you use the word 'pass' somewhe

=IF(COUNTIF(B1:B5,"pass")=5,SUM(A1:A5),0)

Where B1:B5 contains 'pass' for a pass and anything else for "still
developing" - you can't fail anyone these days!


--
Steve

"jhumphreys" wrote in message
...

Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JH


--
jhumphreys
------------------------------------------------------------------------
jhumphreys's Profile:
http://www.thecodecage.com/forumz/member.php?userid=470
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=112852

  #3   Report Post  
Old July 6th 09, 09:28 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Creating a pass of fail condition


jhumphreys;404767 Wrote:
Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JHHi and welcome to the code cage!, supplying a workbook would be much

better as we can help you directly with your particular structure,
however, attached is a sample of how to use sumproduct, take a look at
it and post back any questions or queries


+-------------------------------------------------------------------+
|Filename: Sumproduct example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=172|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112852

  #4   Report Post  
Old July 6th 09, 09:39 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,646
Default Creating a pass of fail condition

=IF(COUNTBLANK(A1:A5)0,0,SUM(A1:A5))
Regards,
Stefi

„jhumphreys” ezt *rta:


Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JH


--
jhumphreys
------------------------------------------------------------------------
jhumphreys's Profile: http://www.thecodecage.com/forumz/member.php?userid=470
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112852


  #5   Report Post  
Old July 6th 09, 09:39 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 913
Default Creating a pass of fail condition

On Mon, 6 Jul 2009 08:24:58 +0100, jhumphreys
wrote:


Hi there
I need to use a sum function that only happens if a certain condition
exist, this condition being that every number in a column has to be over
a certain level, here is the scenario:

Students have to take a series of 5 parts of an examination tests, each
part is assessed and a score awarded, the scores for all 5 are summed
and an overall mark awarded for their performance. The condition is
though that they HAVE to have acheived a pass in each test, If they fail
in at least one text, it doesn't sum or just returns a zero result.

Anyone suggest how to do this?

Thanks
JH



If your scores are in cells A1 to A5 and your levels that the scores
have to be over are in cells B1 to B5, then try the following formula:

=AND(A1:A5B1:B5)*SUM(A1:A5)

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

If the limits are the same for each test you can use

=AND(A1:A5B1)*SUM(A1:A5)

instead. The common limit for all tests is put in B1 (or directly into
the formula).

Hope this helps / Lars-ke


  #6   Report Post  
Old July 6th 09, 10:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Creating a pass of fail condition


Thanks to everyone for their contributions,the final product when
devised will not be exactly as the scenario depicted, but with all these
possible solutions it gives me a greater range of options than I
expected to find. Appreciate the help.
Rgds JH


--
jhumphreys
------------------------------------------------------------------------
jhumphreys's Profile: http://www.thecodecage.com/forumz/member.php?userid=470
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112852

  #7   Report Post  
Old July 7th 09, 01:26 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Creating a pass of fail condition


jhumphreys;405776 Wrote:
Thanks to everyone for their contributions,the final product when
devised will not be exactly as the scenario depicted, but with all these
possible solutions it gives me a greater range of options than I
expected to find. Appreciate the help.
Rgds JH


Glad we could be of help!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112852



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula help - Sum results Pass or Fail Nadine Excel Worksheet Functions 9 August 6th 13 10:20 AM
Need Formula to Know Percent of a row of Pass/Fail data Moriah Excel Worksheet Functions 3 June 16th 09 09:03 PM
Graphing Text: Pass or Fail Robert Charts and Charting in Excel 3 April 12th 09 11:41 PM
Creating visible Pass / Fail areas K.P.D Charts and Charting in Excel 1 October 24th 08 10:36 AM
pass fail Tricia New Users to Excel 3 January 20th 05 03:45 PM


All times are GMT +1. The time now is 07:54 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017