Match / CountIfs or other?

[AshleyLeach]

Is anyone able to help me with a formula that I have been struggling with for some time now. I would like to calculate the value added score across the whole group of learners from their Projected Grades (Column M) against their Minimum Expected grade (Column L). I would like to see how many of them are above target Cell V5, how many are on target V6 and below target V7. Heres the tricky bit, the grades values are F, P, HP, M, HM, D, HD as seen on the DataSheet. These presented Fail, Pass, High Pass, Merit, High Merit, Distinction, HighDistinction. 

Any help would be greatly appreciated.

AutoCal - Test Value Added.xlsx

 /  

[#2]

@AshleyLeach 

Since you've already rendered the names anonymous, could you go the next step toward helping us help you by posting not an image but the actual file. Put it on OneDrive or GoogleDrive with a link pasted here that grants access to it.

[#3]

Thank you, I have attached it to the message above now.

[#4]

@AshleyLeach 

See the attached version.

I defined a named range Grades on DataSheet and used that in the formulas.

[#5]

Thank you, that looks great and completely makes sense. Would there be a way to calculate the score as a total; for example a learner who has projected grade as P but the Minimum Expected grade should be HM that would be -2 points. Another student who has projected grade as M and Minimum Expected grade a HP this would be +1.

[#6]

@AshleyLeach 

Try

=SUM(IFERROR(XMATCH(M13:M26,Grades),0)-IFERROR(XMATCH(L13:L26,Grades),0))

[#7]

@AshleyLeach 

If I understand the goal correctly, the objective is finding how many grades (Projected - MinExpected) fall in one of three categories and then summing the + or - for each category.

A 365 solution:

=LET(
    MinExpected, XMATCH(L13:L26, DataSheet!A2:A10),
    Projected, XMATCH(M13:M26, DataSheet!A2:A10),
    diff, IFERROR(Projected - MinExpected, 0),
    bucket, SIGN(diff),
    Total, LAMBDA(i, SUM(FILTER(diff, bucket = i, 0))),
    BYROW({1; 0; -1}, Total)
)

[#8]

Thank you all, I appreciated the help massively.

Follow up question, is it possible to copy a whole row to a new spreadsheet on a single cell critiera?

Something like if any cell in A Column has CF222 copy from this sheet to a different sheet.

Or,

If any cells in F Column has '19 and over' then copy it to anther sheet in the same document? 

thanks, 

[#9]

Thank you all, I appreciated the help massively.



Follow up question, is it possible to copy a whole row to a new spreadsheet on a single cell critiera?



Something like if any cell in A Column has CF222 copy from this sheet to a different sheet.



Or,



If any cells in F Column has '19 and over' then copy it to anther sheet in the same document?


thanks,

[#10]

@AshleyLeach 

You can use FILTER.

Here's a basic example where records with "red" in a table's 'color' column are retrieved:

=FILTER(Demo,Demo[Color]="red")

[#11]

Thank you @Patrick2788 that works great. When I have used this in my document, it is copying blank cels as 0 into the other sheet. Is there anyway of removing these?

Also, is there a way of using this formula to search across more than one sheet a data row?

Huge thanks,

[#12]

@AshleyLeach 

The easiest way to suppress the 0s being returned is to use custom cell formatting which uses four different slots separated by semicolons:

Positive;Negative;Zero;Text

In this example below, the custom cell formatting code is 0;-0;;General

Notice how the third slot is skipped - there are two consecutive semicolons.  Additionally, Excel will shorten the formatting code by dropping "General" but I've included it for illustrative purposes.

Re: filtering for multiple sheets. It's do-able but will require the data be consolidate with VSTACK using a 3D reference.

[#13]

@AshleyLeach 

Another option is to return empty strings instead of blanks

=LET(
    f, FILTER(Demo, Demo[Color] = "red"),
    SUBSTITUTE(f, "", "")
)