Search code examples
excellibreoffice-calc

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.

Solution

  • 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)