• 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.

Calculation of marks using single formula

jb

Member
Dear Helpers,
I have an excel sheet with students data like roll number, name and the answers they have given in quiz. Sample file: A2 to E5. c2, d2 and e2 are correct answers of Q1, 2 and 3.

I want to calculate total marks obtained by each student.
For that i have created table in H3 to J5 where I have calculated marks of individual question and then applied sum formula in k column.

Is there any single formula with which this calculation can be done directly without creating extra table?

Please also help me to calculate quiz marks if weightage of each question is different.

Sample file attached
 

Attachments

  • test.xlsx
    10.8 KB · Views: 9
Last edited:
Things are about to change for Excel 365 users because it will soon be possible to process the rows in an array and return an array of results rather than relying upon copy down. Meanwhile
Code:
= MMULT( SIGN(answer=correct), {1;1;1} )
will give the same result but at the expense of having to use an excessively mathematical function MMULT.
75621
 
@Peter Bartholomew I presume you are referring to something in the insider version. Could you please share it for those of us who are insiders? I'd like to try whatever it is. Thanks.
 
Hi Ali
Yes it was the recent announcement
Announcing LAMBDA Helper Functions (microsoft.com)
that I was referring to.

The new functions have yet to be activated on my instance of the Insider beta channel, so I do not have personal experience of them. My understanding is that MAP will allow one to feed the terms of an array into a Lambda function one by one and then build an array of the results. This is just about possible to achieve now with recursive Lambda function calls, but that is to take spreadsheet calculation from something that was intended for non-programmers to the level of allowing fiendishly complicated solutions. In the present case BYROW will offer a cleaner approach to summing rows.
 
Is there a way with LET? That’s something I’ve started playing with recently. I’ve seen a lot of people using FILTER.XML, too, but have not started delving into that yet.
 
No, I don't think LET allows you to do anything that is not already possible. What it does is allow formulas to be far more coherent and legible, as well as reducing the need for helper ranges. A simple nested formula I can cope with, but as soon as it needs to go deeper, I turn to LET. I hate trying to pair up trailing parameters with the functions. Something that I find helpful using LET is to use Alt/Enter to pair of the partial formulas with the names assigned to them.

LAMBDA is where the magic starts. Simple applications allow you to build formulas and then apply them to different datasets. Lambdas that call other Lambdas, especially themselves, enable calculations to be performed that are next to impossible without them, including the splitting of text by separator that otherwise requires the somewhat idiosyncratic FILTER.XML. It is my hope that the new helper functions will allow me to exploit this functionality without needing a PhD in computer science!

Ways of performing Accumulation with Dynamic Arrays - Microsoft Tech Community
This is something I posted that might serve as a warning rather than encouragement. It really needs to become simpler, and I think it just has!
 
Back
Top