Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having Relative Answers With Altered Info
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:D27 in the Grades worksheet. Write the formula in cell B3 and use the fill handle to copy them through the rest of the cells. -- ladeda16 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Altered Column Width | Excel Discussion (Misc queries) | |||
Altered Column Width | New Users to Excel | |||
Altered Column Width | Excel Worksheet Functions | |||
highlighting altered cells | Excel Discussion (Misc queries) | |||
jpg Image Altered when emailed to recipient. | Excel Discussion (Misc queries) |