CODEWITHSUNDEEP
excellibreoffice-calc
halirutan
halirutan

Reputation: 4341

Gather/Select rows for calculation

My question is embarrassingly simple, but a colleague asked me, and I almost never use Excel. In Mathematica, I can solve this within seconds. Let us assume a very simple arrangement consisting of a patient ID, a year of measurement, and a value:

enter image description here

Question 1: How can I create a new table consisting only of the first measurements? That is, for ID 1 the 2014 row, for ID 2 the 2015 row and for ID 3 the 2016 row.

Question 2: How can I create a new table that has only one line for each ID and where all found Values are used with a function like MEDIAN()? That is for ID 1 the median of 12 and 23, for ID 2 the median of 32 and 16 and for ID 3 just 4.

Upvotes: 0

Views: 43

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Assuming your table is ordered by date (which it is in the picture), then the first question can be solved using a VLOOKUP

=VLOOKUP("1",A1:C6,3,0)

Replace "1" which whichever cell contains the ID 1 in your new table.

For question 2, you can use the following formula, again replacing =1 with whichever cell contains ID 1 in your new table

{=MEDIAN(IF(A1:A6=1,C1:C6,""))}

Note, this is an array formula, you don't type the curly brackets {} directly, but simple press CTRL+SHIFT+ENTER, rather than just ENTER when entering the formula

This works by creating an array like this {12,23,"","",""} and taking the median of those numbers. MEDIAN ignores the strings so gives you the median of 12 and 23, as required (example for ID 1)

Upvotes: 1

Related Questions