• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

format a cell to compute a point value for a score.

Raoul R. Donati

Raoul R. Donati

Member
I am having trouble trying to figure out a formula for the rubric. Here is the formula I have tried but with no luck.
=IF(M4<=TIME(0,8,0),"10",IF(M4>=TIME(0,8,1),AND(M4>=(L4(0.03),"9",IF(M4<=TIME(0,8,1),"8",IF(M4>=TIME(0,10,31),AND(M4>=(0.1*L4),"7",IF(M4>=TIME(0,10,31),"6"))))))))
 
Raoul R. Donati said:
I am having trouble trying to figure out a formula for the rubric. Here is the formula I have tried but with no luck.
=IF(M4<=TIME(0,8,0),"10",IF(M4>=TIME(0,8,1),AND(M4>=(L4(0.03),"9",IF(M4<=TIME(0,8,1),"8",IF(M4>=TIME(0,10,31),AND(M4>=(0.1*L4),"7",IF(M4>=TIME(0,10,31),"6"))))))))
Click to expand...
Hi,

Formula like are; generally, quite simple but I'm struggling to understand the requirement here. Can you explain what this means

AND(M4>=(L4(0.03),"
 
I am using a rubric to assess students. I want to set up a spread sheet to keep track of my student's times and be able to give a score each time they this test.

10=Better than the healthy zone for your age or you're in the Healthy zone and improved by more than 10% =IF(M4<=TIME(0,8,0),"10". The Healthy Fitness zone is 8:00-10:30. By the way, this part of the formula works great.

9=In the healthy zone and improved by 3%. This part of the formula is a bit more difficult. IF(M4>=TIME(0,8,1),AND(M4>=(L4*0.03),"9". Basically, if a student scores between 8:30 and 10:30 and increases there time by 3% they can earn a 9. This were I am having the problem. I can't figure the formula for this part of the rubric.

8=In the healthy zone, but did not show improvement =IF(M4<=TIME(0,8,1),"8"

7=Not in the Healthy zone, but showed more than 10% improvement. =IF(M4>=TIME(0,10,31),AND(M4>=(0.1*L4),"7"

6=Not in the healthy zone, and did not improve by at least 10% but participated =IF(M4>=TIME(0,10,31),"6"))))))))

Your help would be very helpful
 
Hi Raoul ,

The AND function would need to be used like this :

IF(AND(M4>=TIME(0,8,1),M4>=(L4*0.03)),"9")

The AND function surrounds the two conditions which need to be ANDed viz.

M4>=TIME(0,8,1)

M4>=(L4*0.03)

Of course , without looking at the data , I cannot say whether the latter condition is checking for a 3% improvement ; should it not be :

M4>=(L4*(1 - 0.03))

assuming that L4 has the previous time , and M4 is the current time.

Narayan
 
It still doesn't formulate. There is an error message stating "AND expects boolean values. "9" is a text and cannot be coerced to a boolean."
 
Hi Raoul ,

If you can upload your workbook , you will get a solution in the very next post ; as it is , I hope someone else can help.

Narayan
 
Hi Raoul ,

See if this works the way you want it to :

=IF(ISBLANK(M4),"No Data",IF(M4<=TIME(0,8,0),10,IF(M4<=TIME(0,10,30),IF(M4<=L4*(1-0.03),9,8),IF(M4<=L4*(1-0.1),7,IF(M4>0,6,"Error")))))

Narayan
 
Your formula worked great:) Thank you. I have one more formula I need help with. I am using the rubric as before but instead of time it is laps. The healthy fitness zone is 62-95 laps
10=96 or better
9=In the healthy zone and showed 3% improvement.
8=In the healthy zone, but did not show improvement.
7=Not in the Healthy zone, but showed more than 10% improvement
6=Not in the healthy zone, and did not improve by at least 10% but participated.

I tried to modify your formula but it did not work.
=IF(ISBLANK(O2),"NoData",IF(O2>=96,10,IF(O2<=95,IF(N2<=4*(1-0.03),9,8),IF(O2>=N2*(1-0.1),7,IF(O2>0,6,"Error")))))

9 would not show up.
 
Hi Raoul ,

I have inserted the formula ; I have also converted the range to a table ; I am not sure whether this feature is available in Google Spreadsheets , but if you plan to use this in Excel , I suggest you switch to using Excel tables ; there are so many advantages to using them , the least of which is that you never need to copy formulae down , as you introduce fresh data rows.

Narayan
 

Attachments

Narayan,

First of all, thank you for your help. You have helped me very much. There is still one minor glitch in both excel and google. Anytime I enter a bench mark score that is in the healthy fitness zone and then enter one less in the next column it does not give the correct score. For example, let's say 75 is the bench mark column. If I enter 74 in the next column, the score should read 8. Instead the spread sheet scores it has a 9. A 9 should appear if the students achieved 82.5 or better. Is there a way to correct this?
 
Hi Raoul ,

I have tried with a range of values between 63 and 95 in the bench mark column ( column K ) and the actual column ( column L ) , and every time I have got 8.

Can you say which formula you are referring to ? Is it the formula in column M ?

Narayan
 
Hey Narayan,

Let's use your excel spread sheet as a reference. If I put 75 in column Q and 74 in column R the score should read 8. The spread sheet will score it as a 9. A 9 should not appear until there is a score of 78.
 
Hi Raoul ,

The problem is due to something else !

You have two measures in your system ; one where less is better , and the other where more is better.

Your time measures are of the former type , while the lap measure is of the latter type.

The same formula cannot be used for both ; in one case , you have to use subtraction to check for the 3 % and 10 % improvement , while in the other case , you need to use addition.

These two formula will therefore have to be used appropriately , depending on the type of measure you are evaluating.

=IF(ISBLANK(L2),"No Data",IF(L2>=96,10,IF(L2>=62,IF(L2>=K2*(1+0.03),9,8),IF(L2>=K2*(1+0.1),7,IF(L2>0,6,"Error")))))

The above applies to a measure where more is better.

=IF(ISBLANK(O2),"No Data",IF(O2<=TIME(0,8,0),10,IF(O2<=TIME(0,10,30),IF(O2<=N2*(1-0.03),9,8),IF(O2<=N2*(1-0.1),7,IF(O2>0,6,"Error")))))

The above applies to one where less is better.

If you wish to take the first one , and apply it to a measure where less is better , change the addition signs to subtraction signs and change the comparison signs , as follows :

=IF(ISBLANK(R2),"No Data",IF(R2<62,10,IF(R2<=95,IF(R2<=Q2*(1-0.03),9,8),IF(R2<=Q2*(1-0.1),7,IF(R2>0,6,"Error")))))

Narayan
 
Narayan,
You are a Genius!
I used the formula and it worked like a charm.
=IF(ISBLANK(O2),"No Data",IF(O2>=96,10,IF(O2>=62,IF(O2>=N2*(1+0.03),9,8),IF(O2>=N2*(1+0.1),7,IF(O2>0,6,"Error")))))

Do you think you can make formula to grow hair?
 
Hey Narayan,

I just finished testing with my students and the spreadsheet works awesome. I have another question. Lets say I would like to use a similar spreadsheet but the cell would have to differentiate between a male score and a female score. I would use a column for gender. My question is if I designate M for males and F for females, could I set a cell to recognize the difference between the genders and score the test?
 
You must log in or register to reply here.
Back
Top