Multiple functions in one cell?

[Redwrex]

I have a table showing student results for tasks

I've had help to get the cells to add up and display either a total of all scores OR if an N or NS appears, a N in a cell (eg D3)

Is there a way to make each task out of different totals?

So in H28 and accross, I could say tasks were out of 10 or 32 or 15. Could a formula show that in H3, the child got 4 out of 10 and it shows as 4 in D3. In I3, the student got 5 out of 11 and I want to add that to H3 but make it so that they are comparative answers?????? Does this make sense?

[ORoos]

Hi Redwrex,
Could you upload the sample workbook without the sheet protected so we can work with it.

[Redwrex]

I have made that change.

Sorry, I uploaded the wrong file!

[bebo021999]

I remembered this.
Could you manual mockup some expected results in H28 and accross?

[Redwrex]

Sure,

I've added a 'total marks available row (28)

[ORoos]

On the sheet '9A' you have a sample in row 3.
Would it be possible to do a 'mock-up' in a row below on what exactly you like to see based on the data in row 3.
In H3 you have the value '4' (and other results across to the right). If this is entered in this cell, then you can't have a formula there to show something else. If the result '4' comes from elsewhere then a formula can.

[bebo021999]

Could you explain how value in row 28 come, i.e, 5;10;10;35...

[Redwrex]

So, it is a total available marks for that task.

In H3, it's 4 out of 5 marks
In I3, it's that number out of 10 marks, etc

[GeoffW283]

So I THINK you are you looking for D3 to be an overall percentage? Correct?

That is: the total actual marks = sum(H3:Q3) divided by the available marks = sum(H28:Q28) or in the case of your example: 162/275 = 59%

If so then accommodating the indexing that D3 is already doing the D3 formula would become (I think):
=IFERROR(SUMPRODUCT(OFFSET($H3:$Q3,,MATCH(D$2,$H$1:$AU$1,0)-1,)+0) / SUMPRODUCT(OFFSET($H$28:$Q$28,,MATCH(D$2,$H$1:$AU$1,0)-1,)+0),"N")

If you format D3 as a percentage this yields 59%

[AliGW]

Is it a weighted average percentage that you want?

[Redwrex]

Weighted would be great.

I'd like the last 2 columns in each section to be weighted more than the previous 8.

The second last column weighted at 25% and the last column at 40%

[AliGW]

I'm afraid you will need to be more specific than that.

What should the weighted scores be for each test? What is the relative weighting for each test?

Administrative Note:

We would very much like to help you with your query, however it is now clear that the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)