Forum Discussion
Match / CountIfs or other?
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
/
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.
- Thank you, I have attached it to the message above now.
See the attached version.
I defined a named range Grades on DataSheet and used that in the formulas.
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) )- 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,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")
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,
