# Thread: Mean Marks (Excel XP)

1. ## Mean Marks (Excel XP)

I have been asked to automate the calculations with the results shown on the attached. I can see that "A "stands for absent and that the first of the last three columns will read correctly if I adjust the totals to exclude the individual totals where an "A" is present. What I cannot see is how I set that up using a function so that the effect of an "A" entry is calculated automatically. I can imagine that a column(s) or a row(s) or both may be needed to achieve the target. I cannot think that a macro should be needed. Also I have trouble understanding the relationship between the last column and the two that precede it. I have played around with solver looking for a weighting but have failed.

If anyone can point me in the direction of resolving this mystery to me, I will be grateful

2. ## Re: Mean Marks (Excel XP)

Welcome to the lounge.

I am afraid I don't understand what you are trying to calculate. You have no formulas in your spreadsheet so I do not know where the numbers come from.

The excel stat functions, (average (=Mean), min, max, etc) will ignore text and calculate correctly.

Could you elaborate on what you need?

Steve

3. ## Re: Mean Marks (Excel XP)

Thank Steve,

The last three columns were supplied by my class master and are the target area.

What he wants is that any change in marks crank through to give numbers which reflect his given numbers. In other words he wants to see formualae which support his numbers given in the last three columns which I guess are arrived at by code on his sheet.

I understand what should produce the first of the last three columns labeled TJ 20%. The next is I guess is given by student result on examinations. Given these two inputs I am perplexed by his third column.

If you can help me on the first of the last three columns, I will be a big step ahead. The last would be cream.

I think that there may be a solution with macros but we are asked not to use those!

I hope this will help us both,

Peter

4. ## Re: Mean Marks (Excel XP)

You have not explained what those numbers are or what they represent.

Without understanding what the numbers in those are, how can I give you a formula which will give the appropriate number?

Steve

5. ## Re: Mean Marks (Excel XP)

Thanks, Steve,

The area of confusion concerns the last three columns.

I have already formatted the the rest of the sheet to reflect my masters's requirement.

The first of those last three columns raises the problem of how how I reach the numbers he has given which I have copied to the sheet you have.

I can see that when an A is present, that there should be a reduction in the total divisor to reflect that absence which is confirmed by the numbers supplied in the first of the last three columns.

My problem is getting the conditionality to work on all cells of the row. The "if" condition works fine on a single cell.

On the last columns, there is a matter of "weighting" which I have not been able to work out. The hint is 20% but I cannot create a formula which delivers the numbers which confuse both of us and are unexplained by my teacher.

I guess am missing some simple obvious points.

The unexplained cells are those that I am required to deliver.

I hope that this explains what I am trying to say.

Regards

Peter

6. ## Re: Mean Marks (Excel XP)

<P ID="edit" class=small>(Edited by tony55 on 23-Apr-05 20:11. Modified the file again to remove the redundant macro from this test file to stop the macro warning messazge when opening the file.)</P>Hi Peter & welcome to the Lounge

I have managed to work out a method to re-create the values for the TJ20% column but I am still trying to figure out how the other 2 columns are derived. Though it is called TJ20% I never used 20% in the calculations, instead they are weighted based upon the maximum score possible for the tests taken. See the attached file.

7. ## Re: Mean Marks (Excel XP)

I do not understand at all where the numbers come from.

I assume you are looking for a formula in cols x, y, and z which based on the other columns, give the numbers listed.

You will have to provide us more information on what those numbers mean...

Steve

8. ## Re: Mean Marks (Excel XP)

You can get rid of the intermediate calcs if you just use in col X (and copy it down the rows)

<pre>=SUM(D6:W6)/SUMIF(\$D6:\$W6,">0",\$D\$5:\$W\$5)*100</pre>

Steve

9. ## Re: Mean Marks (Excel XP)

Clever! It can be done without the intermediary formulas too: for example, The TJ20% value for Antoine Marc can be calculated as

=SUM(D6:W6)/SUMPRODUCT(ISNUMBER(D6:W6)*(D6:W6>0)*(\$D\$5:\$W\$5))* 100

10. ## Re: Mean Marks (Excel XP)

Thank you Hans and Steve,

I am in Brussels and it is late Saturday night.

I will study your impressive calculations in the morning..

The column marked "examen" is given by the exam results, I think. The problem remains of how to reach the last column given the two proceeding columns.

Please accept my thanks for your attention. I will return after working out the meaning of what you have already sent me.

Peter

11. ## Re: Mean Marks (Excel XP)

It was Tony who solved it first! Still no idea how the last two columns work. You MUST provide some information about what's behind them!

12. ## Re: Mean Marks (Excel XP)

I think I have solved the problem, I have detailed below the assumptions I have made in reaching the solution.

Columns D-W represent tests taken throughout the year. Column X is used to calculate a weighted score based on these tests, the formula to calculate this is:

<big><code>=SUM(D6:W6)/SUMIF(\$D6:\$W6,">0",\$D\$5:\$W\$5)*100</code></big>

Column Y is the result for an exam taken at the end of the year. Column Z is the final result based on 20% of the tests through the year plus 80% of the final exam and the formula is:

<big><code>=0.2*X6+0.8*Y6</code></big>

Updated file attached.

13. ## Re: Mean Marks (Excel XP)

I now see that the penultimate column is given by an examination result and that the class tests are weighted at 20% against the exam at 80% to give the "Global" mark.

The penny has dropped on the sumproduct function. I guess it works on the isnumber giving a "1" or "0" result which when put through the sumproduct function weeds out the zero sums leaving the reduced divisor to fit the individual student..

Peter

14. ## Re: Mean Marks (Excel XP)

Hans,

I have now come to terms with your formula save the element *(D6:W6>0). The whole calculation seems to work with or without this item in all circumstances. Am I missing something?

Peter

Peter

15. ## Re: Mean Marks (Excel XP)

Steve's formula is shorter and more efficient, so use that.

#### Posting Permissions

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