# Thread: function test challenge (Excel 2003)

1. ## 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

2. ## 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,"*","")

3. ## Re: function test challenge (Excel 2003)

excellent excellent jezza

thanks kitty
xx

4. ## 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

5. ## 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

6. ## 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

7. ## Re: function test challenge (Excel 2003)

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

8. ## 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. ## 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

10. ## 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. ## 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
•