Marksheet


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 17th May 2010, 17:54
Salza's Avatar
Salza
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Marksheet
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

Having a problem to get a correct formula for overall passing.

Marks are keyed into the following cells for all the 26 subjects.

Cell F8 = Subject 1
Cell H8-R8 = Subjects 2-7
Cell T8-BE8 = Subject 8-26

To pass the overall exam - a student must pass FIVE subjects (Subject
1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

Passing mark for each subject is 36.

Can someone help me with the formula? Thank you.



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #2  
Old 17th May 2010, 18:11
Rick Rothstein's Avatar
Rick Rothstein
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Marksheet

------ Register to get rid of these "In Post" ads! ------


I think this does what you want...

=AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)

but I do note that your H8:R8 and T8:BE8 ranges span more columns that there
are subject to fill them... note sure how, or if, that affects the formula.

--
Rick (MVP - Excel)



"Salza" <zainuddinz@gmail.com> wrote in message
news:155de996-e65c-4b19-bcfc-7603bfad54c8@e34g2000pra.googlegroups.com...
> Having a problem to get a correct formula for overall passing.
>
> Marks are keyed into the following cells for all the 26 subjects.
>
> Cell F8 = Subject 1
> Cell H8-R8 = Subjects 2-7
> Cell T8-BE8 = Subject 8-26
>
> To pass the overall exam - a student must pass FIVE subjects (Subject
> 1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).
>
> Passing mark for each subject is 36.
>
> Can someone help me with the formula? Thank you.





  #3  
Old 17th May 2010, 19:03
Salza's Avatar
Salza
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Marksheet

------ Register to get rid of these "In Post" ads! ------


On May 18, 1:08†am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I think this does what you want...
>
> =AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)
>
> but I do note that your H8:R8 and T8:BE8 ranges span more columns that there
> are subject to fill them... note sure how, or if, that affects the formula.
>
> --
> Rick (MVP - Excel)
>
> "Salza" <zainudd...@gmail.com> wrote in message
>
> news:155de996-e65c-4b19-bcfc-7603bfad54c8@e34g2000pra.googlegroups.com...
>
> > Having a problem to get a correct formula for overall passing.

>
> > Marks are keyed into the following cells for all the 26 subjects.

>
> > Cell F8 = Subject 1
> > Cell H8-R8 = Subjects 2-7
> > Cell T8-BE8 = Subject 8-26

>
> > To pass the †overall exam - a student must pass FIVE subjects (Subject
> > 1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

>
> > Passing mark for each subject is 36.

>
> > Can someone help me with the formula? Thank you.



Thanks a lot, Rick. That works fine. Great help from you,
For every marks, there is a column for grading too. That's why there
are many columns.





The Code Cage Advertisment
Advertisement
  #4  
Old 17th May 2010, 19:47
Rick Rothstein's Avatar
Rick Rothstein
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Marksheet

------ Register to get rid of these "In Post" ads! ------


I repaired the ordering of the responses (so my comments are below), but it
is always a good idea to follow the posting style (top posting or bottom
posting your answers) to match the style of the first responder... that way
people looking the thread up in the future (via the Google archives) will be
able to follow the flow of responses in the order they were posted.

"Salza" <zainuddinz@gmail.com> wrote in message
news:d1c8d50e-bdd0-4176-8d39-4a37777a5cdc@42g2000prb.googlegroups.com...
>> "Salza" <zainudd...@gmail.com> wrote in message
>>
>> news:155de996-e65c-4b19-bcfc-7603bfad54c8@e34g2000pra.googlegroups.com...
>>
>> > Having a problem to get a correct formula for overall passing.

>>
>> > Marks are keyed into the following cells for all the 26 subjects.

>>
>> > Cell F8 = Subject 1
>> > Cell H8-R8 = Subjects 2-7
>> > Cell T8-BE8 = Subject 8-26

>>
>> > To pass the overall exam - a student must pass FIVE subjects (Subject
>> > 1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

>>
>> > Passing mark for each subject is 36.

>>
>> > Can someone help me with the formula? Thank you.

> On May 18, 1:08 am, "Rick Rothstein"
>>
>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> I think this does what you want...
>>
>> =AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)
>>
>> but I do note that your H8:R8 and T8:BE8 ranges span more columns that
>> there
>> are subject to fill them... note sure how, or if, that affects the
>> formula.
>>
>> --
>> Rick (MVP - Excel)

>
>
> Thanks a lot, Rick. That works fine. Great help from you,
> For every marks, there is a column for grading too. That's why there
> are many columns.


What I wasn't sure of was if "extra" entries in the columns (the grading if
I understand you correctly) won't affect the counts for any particular
range). For example, in Columns H through R, if you had a single mark along
with a grade for it, then my formula would return the wrong result... it
would see the mark and its grade as two separate entries to be counted even
though there is only one subject filled in in the range. That was my concern
and the reason I added the part at the end of my post.

--
Rick (MVP - Excel)




  #5  
Old 17th May 2010, 20:00
GS's Avatar
GS
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Marksheet

------ Register to get rid of these "In Post" ads! ------


$0.02
I have a student grades manager app that does similar in that it groups
scores by subject context but tallies grades in a separate area where
each group is assigned a 'weight' in the final grade. Output is
Weight%, Grade%, and PointAvg. Avoiding calc errors in the grouped
scores precludes that grades must not be stored there or they'll be
included in the tallies for the group.

Garry
--

Rick Rothstein laid this down on his screen :
> I repaired the ordering of the responses (so my comments are below), but it
> is always a good idea to follow the posting style (top posting or bottom
> posting your answers) to match the style of the first responder... that way
> people looking the thread up in the future (via the Google archives) will be
> able to follow the flow of responses in the order they were posted.
>
> "Salza" <zainuddinz@gmail.com> wrote in message
> news:d1c8d50e-bdd0-4176-8d39-4a37777a5cdc@42g2000prb.googlegroups.com...
>>> "Salza" <zainudd...@gmail.com> wrote in message
>>>
>>> news:155de996-e65c-4b19-bcfc-7603bfad54c8@e34g2000pra.googlegroups.com...
>>>
>>> > Having a problem to get a correct formula for overall passing.
>>>
>>> > Marks are keyed into the following cells for all the 26 subjects.
>>>
>>> > Cell F8 = Subject 1
>>> > Cell H8-R8 = Subjects 2-7
>>> > Cell T8-BE8 = Subject 8-26
>>>
>>> > To pass the overall exam - a student must pass FIVE subjects (Subject
>>> > 1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).
>>>
>>> > Passing mark for each subject is 36.
>>>
>>> > Can someone help me with the formula? Thank you.

>> On May 18, 1:08 am, "Rick Rothstein"
>>>
>>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>>> I think this does what you want...
>>>
>>> =AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)
>>>
>>> but I do note that your H8:R8 and T8:BE8 ranges span more columns that
>>> there
>>> are subject to fill them... note sure how, or if, that affects the
>>> formula.
>>>
>>> --
>>> Rick (MVP - Excel)

>>
>>
>> Thanks a lot, Rick. That works fine. Great help from you,
>> For every marks, there is a column for grading too. That's why there
>> are many columns.

>
> What I wasn't sure of was if "extra" entries in the columns (the grading if I
> understand you correctly) won't affect the counts for any particular range).
> For example, in Columns H through R, if you had a single mark along with a
> grade for it, then my formula would return the wrong result... it would see
> the mark and its grade as two separate entries to be counted even though
> there is only one subject filled in in the range. That was my concern and the
> reason I added the part at the end of my post.






The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
marksheet

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

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


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.19029 seconds
  • Memory Usage 6,382KB
  • Queries Executed 19 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (3)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (5)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (5)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_ctoptip
  • (5)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (1)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php
  • ./includes/functions_prefix.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete