Counting occurences between a certain value based upon unique identifier

[excelguruwannabe]

Hello Everyone. I have this issue that I really don't even know how to start answering. Scenario - In Office30, I want to calculate how many test iterations have occurred between when a student (unique identifier) first fell below an initial A and when they got their next A. I will subsequently calculate the average number of test iterations it takes for a person to get back to an A after failing to achieve an A. I have the data in the vertical view (many other fields), but these are the key aspects (I think). It is important to note, that one student may "perpetually" bounce between an A and a B. I would like to be able to account for each instance that occurs for that one student. Please see the attached workbook for a sampling of the data (hopefully the file attached-it was kciking my butt).

Thanks,

Excelguruwannabe

[AliGW]

Administrative Note:

Office30

There is no such Excel product.

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[excelguruwannabe]

Sorry - Microsoft 365

[excelguruwannabe]

MS365 version 2407

[AliGW]

Thanks.

I've looked and don't yet fully understand the concept. I'll look again shortly.

[windknife]

One guess,

Please Login or Register  to view this content.

[excelguruwannabe]

Windknife - I don't understand any of that magic that you suggested, but it almost worked. I noted two items that don't seem to work properly.

Item 1 - for student 2, it looks like it is counting the first instance that they got the "A" (on test2) and rendering that a 1. But since they didn't get an A on test1, this should not be counted.

Item 2 - Regarding the output, do you know how I would go up either summing them or averaging the output. Student 1 - 1 iteration, Student 2 - 1 and 2 iterations (but as discussed above, it should only be 2 iterations), student 5 - 2 iterations, student 6 - 1 and 2 iterations. Would it be some sort of function in the HSTACK?

[AliGW]

This would do Item 2:

Please Login or Register  to view this content.

Can't help with Item 1 - sorry.

[windknife]

Amended formula,

Please Login or Register  to view this content.

I don't know what you want about item 2.
You can upload a sample file with your expected results.

[AliGW]

Maybe this, then?

Please Login or Register  to view this content.

[excelguruwannabe]

AliGW - Yes, this worked wonderfully. Item 2 - solved.

[AliGW]

See the last two posts before yours.

[excelguruwannabe]

Windknife & AliGW Between the two of you, you have solved this excel black magic for me. Thank you so much. I will mark the thread solved.

Excellguruwannabe

[AliGW]

Thanks for the rep.