CODEWITHSUNDEEP
excelexcel-formulaoffsetcountif
Jan Willem
Jan Willem

Reputation: 132

Calculate the number of rows where value is smaller than a respective next offset value

I'm trying to create an Excel-formula that counts the number of times a labeled value is smaller than another value offset down respectively to itself. The image below shows a dataset (left), and a matrix with the labels and offset values (top right). The values E3:H7 are the desired results (plus "/" followed by the number of occurences).

Example E3 means: 4/4 times, value A was SMALLER THAN the next value offset by 1 (a.k.a. the next B).

Another example G4: 2/4 times, value B was SMALLER THAN the next value offset by 3 (a.k.a. the next E)

enter image description here

It would be a combination of COUNTIF and OFFSET i believe, but Matrix-calculations and offset don't seem to work together. I hope anyone can point me in the right direction

Upvotes: 1

Views: 235

Answers (2)

PeterT
PeterT

Reputation: 8557

This is a compound problem to count both the values less than the other values as offsets, and to count the pairs of values being compared.

If you use this formula in Cell E3 and then copy/paste down and across, it should do the trick (the named range Labels is $A$1:$A$20 and the range Values is $B$1:$B$20):

=SUMPRODUCT(--(Values<OFFSET(Values,E$2,0))*(Labels=$D7))&"/"&SUMPRODUCT(--(Labels=$D7)*--(OFFSET(Labels,E$2,0)=OFFSET($D7,E$2,0)))

Upvotes: 0

Jan Willem
Jan Willem

Reputation: 132

I've figured it out. The final formula in E3 (for the value in front of the slash):

=SUMPRODUCT((1*($B$1:$B$20<OFFSET($B$1:$B$20,E$2,0))),1*($A$1:$A$20=$D3))

Upvotes: 0

Related Questions