Hi everyone,
I always feel a bit guilty coming here and asking for help! But I only use it as a last resort when I've tried tried tried. Even writing this explanation has taken an age.
I thought I knew what I had to do. But then I quickly realise, when implementing the solution, that it is far more complex than I was contemplating.
Schools in Scotland need to make sure pupils leave with at least five qualications. I need to group these pupils into categories: 5@3, 5@4 and 5@5. This translates into pupil X has achieved 5 qualifications at Level 3/4/5.
I have a row of subject names along the top. These subjects are three different levels 3-5. Example: Accounting (N5) = Level 5, PC Passport (L4) = Level 4. I can easily count the number of Level 3/4/5 qualifications using =COUNTIF(C10:AG10,"*3*"), but I need that to return and array of 1 and 0 when the condition is hit, I think.
I then have a row of numbers underneath each subject. Any number below 7 means the pupil is passing that subject at that Level. Again, I can easily count those using =COUNTIF(C11:AG11,"<7"). But I also think I need that to return and array of 1 and 0 so I can multiply it against the above array.
I'm trying to find the simplest way of explaining what I require. In my head I'm picturing an array of all the times a particular Level like "4" is found in the subject row {0,0,1,0,0,1...}. Then I think I need to multiply that against an array of the row of grades that are <7 {1,0,0,0,0...}. This would return that the pupil is on track in 0 Level 4 subjects.
So, I've created a wee mock-up with a manual example of what I'm trying to achieve at the top. It goes without saying I could use COUNTIF(C4,"<7")+COUNTIF(E4:F4,"<7")+COUNTIF(H4:I4,"<7")+COUNTIF(K4:L4,"<7") to count the number of level 4 and above a pupil is on-track in, but I'm trying to work on formula that I'll never need to update if subjects are introduced or removed. There are currently 108 subjects so doing this manually has become an absolute headache, but the solution I've been using for 2 years now.
I've tried inserting a mini-sheet but I cant see how this will re-produce my SS:
So I've also attached an image.
Thanks,
Liam
I always feel a bit guilty coming here and asking for help! But I only use it as a last resort when I've tried tried tried. Even writing this explanation has taken an age.
I thought I knew what I had to do. But then I quickly realise, when implementing the solution, that it is far more complex than I was contemplating.
Schools in Scotland need to make sure pupils leave with at least five qualications. I need to group these pupils into categories: 5@3, 5@4 and 5@5. This translates into pupil X has achieved 5 qualifications at Level 3/4/5.
I have a row of subject names along the top. These subjects are three different levels 3-5. Example: Accounting (N5) = Level 5, PC Passport (L4) = Level 4. I can easily count the number of Level 3/4/5 qualifications using =COUNTIF(C10:AG10,"*3*"), but I need that to return and array of 1 and 0 when the condition is hit, I think.
I then have a row of numbers underneath each subject. Any number below 7 means the pupil is passing that subject at that Level. Again, I can easily count those using =COUNTIF(C11:AG11,"<7"). But I also think I need that to return and array of 1 and 0 so I can multiply it against the above array.
I'm trying to find the simplest way of explaining what I require. In my head I'm picturing an array of all the times a particular Level like "4" is found in the subject row {0,0,1,0,0,1...}. Then I think I need to multiply that against an array of the row of grades that are <7 {1,0,0,0,0...}. This would return that the pupil is on track in 0 Level 4 subjects.
So, I've created a wee mock-up with a manual example of what I'm trying to achieve at the top. It goes without saying I could use COUNTIF(C4,"<7")+COUNTIF(E4:F4,"<7")+COUNTIF(H4:I4,"<7")+COUNTIF(K4:L4,"<7") to count the number of level 4 and above a pupil is on-track in, but I'm trying to work on formula that I'll never need to update if subjects are introduced or removed. There are currently 108 subjects so doing this manually has become an absolute headache, but the solution I've been using for 2 years now.
I've tried inserting a mini-sheet but I cant see how this will re-produce my SS:
| trying_to_work_out_key_measures.xlsx | |||
|---|---|---|---|
| AA | |||
| 22 | |||
Sheet1 | |||
So I've also attached an image.
Thanks,
Liam
?
