L
ladeda16
First thing's First- If you get confused by what i say in this question,
you can contact me through AIM messenger, my screename is ladeda063610.
Because i think you have to look at the spreedsheet i have for it to
make sense for you. So, please message me if you can help thanks
Here is my question, how can i use a formula that gathers information
from other worksheets that have been altered differently, so that i
could still get the same answer even if it the info was different.
This is what i need to do: (student's score ) - (days late * 0.05 *
possible points)
This is what i have so far:=(Scores!B3)-('Submit
Times'!B3-B$29*0.05*Grades!B$29)=-38,397. The answer i'm supposed to
get is 10.5
Below is information on why i altered the information. And i attached
my spreadsheet so you know what i did. PM me if you can help me or i
can give you my aim, so you can help me if you can
Dealing with negative results
The information we need is actually how many days students are late in
submitting. This will be indicated by the positive results returned by
the formula you use to find out the date differences. For those who
submitted before the deadline this result will be 0 or negative. We do
not need the negative values as these students will not get any
penalty. Instead of a negative number, we want to put 0 in their
penalty column. Therefore we should find a way to put the value 0 in
the penalty column when the difference of the submission date and the
deadline is negative.
There are several ways to do this. One way is to use the MAX function
which returns the maximum value in a range of cells. For example,
MAX(-5,0) will return 0 and MAX(2,0) will return 2.
Another way of doing this is by using the IF statement.
Using this knowledge, you have to modify the formula you wrote in cells
F3:H27 so that 0 is displayed instead of the negative numbers. You
should use the MAX function with the DAY functions to provide the
formula that will calculate the number of days students are late in
submitting Quiz1, Quiz and MP0 without displaying negative numbers.
Write the formula in F3 use the fill handle to copy the formula into
the area F3:H27. Also, format the cells to show a number, not a date.
Part IV. Adjusting the Scores and showing the statistics
Now that you are done with calculating the number of days people have
been late in submitting, you can apply the penalty to whatever score
they got. Note that this change will affect their average, so the
weighted grade that you calculated in the Score worksheet will not be
applicable any more. Therefore you will need to adjust the scores by
applying the penalty as required. Professor Bumblebee wants the class
statistics to be based on the adjusted score, so the adjustment will be
the base of all the operations to follow.
Professor Bumblebee wishes to penalize a student 5% of the total points
for each day s/he was late. So if a student was 3 days late, then the
penalty would be 3 * 5% = 15% of the total points off her/his score. So
the formula to find the adjusted score is:
(student's score ) - (days late * 0.05 * possible points)
You will use the Grades worksheet to fill in the adjusted scores and
the class statistics based on them.
Step 1: Adjusting the Scores
For this part, you need to reference both the Scores worksheet and the
Submit Times worksheet. Scores has the actual scores that the students
got, Submit Times has the number of days delayed for submission. Using
this information, you have to write a formula that will give the new
score by applying 5% penalty for each day a student has delayed in
submitting and subtracting that from the original score.
You will need to fill in the cells B3
27 in the Grades worksheet.
Write the formula in cell B3 and use the fill handle to copy them
through the rest of the cells.
you can contact me through AIM messenger, my screename is ladeda063610.
Because i think you have to look at the spreedsheet i have for it to
make sense for you. So, please message me if you can help thanks
Here is my question, how can i use a formula that gathers information
from other worksheets that have been altered differently, so that i
could still get the same answer even if it the info was different.
This is what i need to do: (student's score ) - (days late * 0.05 *
possible points)
This is what i have so far:=(Scores!B3)-('Submit
Times'!B3-B$29*0.05*Grades!B$29)=-38,397. The answer i'm supposed to
get is 10.5
Below is information on why i altered the information. And i attached
my spreadsheet so you know what i did. PM me if you can help me or i
can give you my aim, so you can help me if you can
Dealing with negative results
The information we need is actually how many days students are late in
submitting. This will be indicated by the positive results returned by
the formula you use to find out the date differences. For those who
submitted before the deadline this result will be 0 or negative. We do
not need the negative values as these students will not get any
penalty. Instead of a negative number, we want to put 0 in their
penalty column. Therefore we should find a way to put the value 0 in
the penalty column when the difference of the submission date and the
deadline is negative.
There are several ways to do this. One way is to use the MAX function
which returns the maximum value in a range of cells. For example,
MAX(-5,0) will return 0 and MAX(2,0) will return 2.
Another way of doing this is by using the IF statement.
Using this knowledge, you have to modify the formula you wrote in cells
F3:H27 so that 0 is displayed instead of the negative numbers. You
should use the MAX function with the DAY functions to provide the
formula that will calculate the number of days students are late in
submitting Quiz1, Quiz and MP0 without displaying negative numbers.
Write the formula in F3 use the fill handle to copy the formula into
the area F3:H27. Also, format the cells to show a number, not a date.
Part IV. Adjusting the Scores and showing the statistics
Now that you are done with calculating the number of days people have
been late in submitting, you can apply the penalty to whatever score
they got. Note that this change will affect their average, so the
weighted grade that you calculated in the Score worksheet will not be
applicable any more. Therefore you will need to adjust the scores by
applying the penalty as required. Professor Bumblebee wants the class
statistics to be based on the adjusted score, so the adjustment will be
the base of all the operations to follow.
Professor Bumblebee wishes to penalize a student 5% of the total points
for each day s/he was late. So if a student was 3 days late, then the
penalty would be 3 * 5% = 15% of the total points off her/his score. So
the formula to find the adjusted score is:
(student's score ) - (days late * 0.05 * possible points)
You will use the Grades worksheet to fill in the adjusted scores and
the class statistics based on them.
Step 1: Adjusting the Scores
For this part, you need to reference both the Scores worksheet and the
Submit Times worksheet. Scores has the actual scores that the students
got, Submit Times has the number of days delayed for submission. Using
this information, you have to write a formula that will give the new
score by applying 5% penalty for each day a student has delayed in
submitting and subtracting that from the original score.
You will need to fill in the cells B3
27 in the Grades worksheet.Write the formula in cell B3 and use the fill handle to copy them
through the rest of the cells.