|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 6th, 2009, 08:24 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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
July 6th, 2009, 09:22 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
July 6th, 2009, 09:28 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
July 6th, 2009, 09:39 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
July 6th, 2009, 09:39 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
July 6th, 2009, 10:11 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
July 7th, 2009, 01:26 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
