Hello everyone
Attached is a file in which a sequence can be found in column A. For every one of these numbers it is determined whether a test is performed or not. If no test is performed there will be an entry "no result" in column B. If a test is performed the result will either be a success or a failure. So far so good.
The aim is now to determine a rolling success ratio for the last 10 entries of that sequence number, moving down in column C.
E.g. in cell C69, I would look at the last 10 entries that also contain a «-1» under «sequence» and for which there is a result (success or failure). In this case the answer would be 80% (8 successes and 2 failures).
The file is actually a lot longer than shown here and the sequences may range between -7 and +7. I would calculate the success rate of, say the +1 or the -3 in the same way.
The difficulty that throws me off course is that the spacing between the sequence numbers differs, e.g. the -1 entries occur at random and I need to go up the column until I have those 10 entries. The same goes for all the other sequence numbers.
Can anyone help here?
Best regards,
Excel_Arate
Bookmarks