Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    function test challenge (Excel 2003)

    Tutors have asked that, for their own personal guidance they would like to know which client did not achieve a basic 50% result for each test so for example test 1 in cell D17 is based on 20 so if they scored 10 or under the tutor has a * asterisks sign in tutor notes. He/She will later look at these results for their own personal improvement of course teaching, aimed at clients with the most amount of * asterisks.
    i need a function to say IF D17=<10 then the answer is *,IF E17<=30 then the answer is *, IF F17<=25 then the answer is *
    the idea is that the tutor will see * in cell J17 if the student has only one low score, but if the student has two low scores the tutor will see ** and if three low scores *** indicating very poor performance.
    i do note that this function may be impossible but i thought it was a good idea and would like to see if anyone can achieve this result or come up with a better idea hopefully using a symbol to achieve the same goal.

    many thank as always kitty
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: function test challenge (Excel 2003)

    There is probably a more elegant way but this certainly does the job <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Place this in L17 and copy down:

    =IF(D17<=$D$16/2,"*","")&IF(E17<=$E$16/2,"*","")&IF(F17<=$F$16/2,"*","")
    Jerry

  3. #3
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function test challenge (Excel 2003)

    excellent excellent jezza

    thanks kitty
    xx

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function test challenge (Excel 2003)

    I don't know if this is any more elegant than Jezza's but this is a way that does not require IFs (which can be memory hogs)

    =REPT("*",(D17<=$D$16/2)+(E17<=$E$16/2)+(F17<=$F$16/2))

    An additional feature could be to highlight the scores with Cond formatting: Yellow for 1 score, amber for 2 and red for 3.
    Select D17:F56
    Format - cond formatting and set as listed in the attached.

    Steve
    Attached Images Attached Images

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: function test challenge (Excel 2003)

    Nice one Steve, I was toying with REPT() after I posted and was over complicating it with AND/OR...never mind this looks good though.

    However, I was also thinking that if I owned this sheet though I would probably want to identify the exam that the students were having difficulty with so my equation could be changed to:

    =IF(D17<=$D$16/2,"1","")&IF(E17<=$E$16/2,"2","")&IF(F17<=$F$16/2,"3","")

    To allow the results to be

    1
    12
    13
    2
    23
    3
    123

    to aid the eye
    Jerry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function test challenge (Excel 2003)

    I used the coloring of teh conditional formatting to Identify the test

    When the cond formatting is added the individual tests are colored. See attached.

    Steve
    Attached Files Attached Files

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: function test challenge (Excel 2003)

    Yes, NOW I see it <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Jerry

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function test challenge (Excel 2003)

    Yes, I probably should have just attached the XL sheet originally rather than the picture of the Cond format to be more clear of what I was saying.

    The idea was exactly the same as yours: mark the offending tests. It was just different methods to do it

    Steve

  9. #9
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function test challenge (Excel 2003)

    hi there i thought the conditional formatting was a great idea, however following the instructions i cant get it to work
    can you look at it and tell me where im going wrong you gave me the message .....
    "An additional feature could be to highlight the scores with Cond formatting: Yellow for 1 score, amber for 2 and red for 3.
    Select D17:F56
    Format - cond formatting and set as listed in the attached"

    going by the attachment of the condition and your instruction i cant get it to work, as you will see on the sheet i have attached only a small amount of the condition worked
    kitty
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function test challenge (Excel 2003)

    You have changed the format of the worksheet. The "asterisk column" is now J instead of L and each of the formulas need to be adjusted for this.

    Steve

  11. #11
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function test challenge (Excel 2003)

    ha ha of course what a muppet i am. thank you much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •