Find number of 0's in 0/1 collumn depending on username

[thikhai]

Hey excelforum

I need some help with a formula for a data set I’m working on.
I’m looking at data from a surgical simulation, and needs to know how many failed simulations a participant has had within the last 3, 5 and 10 simulations.
So I have a column containing the results (0=fail/1=succes) and another column containing the corresponding usernames used in the simulation.
My problem is that sometimes the usernames are scrambled/mixed (but they all have date_time stamp) and therefore the formula has to be able to consider all previous results by a specific username (even though they might not be the previous rows) and tell me how many fails a user has had within the last 3, 5 or 10 simulations.
Part of the problem is that the usernames are stacked on top of each other in the username collumn. This means that for the first entry of a given username the formula needs to take into account that there is no previous results by this username to analyze.

Hope you guys can help
Thanks!

[Richard Buttrey]

Welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.



Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FDibbins]

I would suggest that you include some sample answers in your file

[Saqib Qureshi]

I have added four Columns to your data:
Column C : Date and Time
Column D-F : will assist in calculations - You can Delete E

Do tell me if this works

Solved - Example.xlsx