lundi 9 avril 2018

Excel COUNTIF where number of cells to meet criteria range is non-contiguous

I am looking to use the =count function (or one of its family) to count how many times a value >2 appears in Column B where the range (number of rows) is dictated by how many rows in Column A are within a pre-set interval from the adjacent cell in Column A. But, the intervals between cells in Column 1 are highly variable. I have seen many answers addressing dynamic ranges but none appear to deal with this scenario.

I have created a mini-dataset (linked photo) as an example. I want to create an equation in Column C to count how many times "2" appears where the range is within a range that is plus or minus 0.2 from the adjacent cell in Column A. So, for C3, the range will be B1:B5 and for C4, B2:B6 (i.e., equal number cells in the range). But for C5, the range will be two cells less: B3:B5.

Example dataset

I appreciate I could do the count manually in this example but my actual dataset comprises many hundreds of cells. I have attempted all sorts of IF statements, including pre-calculating what X+0.2 and X-0.2 are in new columns, without success.

Many thanks in advance for any helpful tips.

Aucun commentaire:

Enregistrer un commentaire