CODEWITHSUNDEEP
excelexcel-formula
user3520815
user3520815

Reputation: 31

How to average difference between many value pairs by index

and thanks for your help! Without helper columns/rows I'm trying to find the average difference in LVEDi between first and last visits of the patients.

E.g., by hand you would work it out as:

(diff for patient a + diff for pt b + diff for pt c)/3 = 
((55-45) + (40-31) + (25-31))/3 = 
(10 + 9 + (-6))/3 =
13/3 =
4.333

I'm sure the answer involves indexing with an array function, but I can't get started on how to tackle it!

Any help?

Thanks!

      A          | B      | C          | D      | E
 01   Patient ID | Date   | First-Last | LVEDi  | LOTS of similar cols
 02   a          | 3/3/03 | LAST       | 55
 03   a          | 1/1/01 | FIRST      | 45
 04   a          | 2/2/02 |            | 65
 05   b          |        |            | 
 06   b          | 3/2/16 | LAST       | 40
 07   b          |        |            | 
 08   b          |        |            | 
 09   b          | 2/1/12 | FIRST      | 31
 10   c          | 1/7/14 | LAST       | 25
 11   c          |        |            | 
 12   c          | 2/2/03 | FIRST      | 31
 13   c          | 2/2/08 |            | 46

Upvotes: 0

Views: 65

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Found a slightly less verbose method:

=SUM(SUMIFS(D:D,C:C,"LAST",A:A,{"a","b","c"})-SUMIFS(D:D,C:C,"FIRST",A:A,{"a","b","c"}))/3

enter image description here

EDIT 1

Building on Lukas' comment:

=(SUMIF(C:C,"LAST",D:D)-SUMIF(C:C,"FIRST",D:D))/COUNTIF(C:C,"LAST")

enter image description here

Upvotes: 1

LukasV
LukasV

Reputation: 328

You can use the * as and AND criterion in arrays, so with MATCH you search for the instance, where both conditions multiplied are TRUE (i.e. 1). The rest is just filling in your calculation:

{=((INDEX(D2:D13,MATCH(1,(A2:A13="a")*(C2:C13="LAST"),0))-INDEX(D2:D13,MATCH(1,(A2:A13="a")*(C2:C13="FIRST"),0)))+(INDEX(D2:D13,MATCH(1,(A2:A13="b")*(C2:C13="LAST"),0))-INDEX(D2:D13,MATCH(1,(A2:A13="b")*(C2:C13="FIRST"),0)))+(INDEX(D2:D13,MATCH(1,(A2:A13="c")*(C2:C13="LAST"),0))-INDEX(D2:D13,MATCH(1,(A2:A13="c")*(C2:C13="FIRST"),0))))/3}

And remember to use CTRL + SHIFT + ENTER to enter the array.

Upvotes: 1

Related Questions