1

To all:

This is my first foray into using a forum for my specific question, so, please, let me know what additional information might be necessary. I am providing some information below; yet, if it is better in a different format, please, let me know.

I am attempting to use a query in Google Sheets to dynamically clean some data that is populated by another source rather than cleaning each time the data are updated.

I would like to have rows with only those ID occurring twice (i.e., once with a Pretest and once with a Posttest). Further, I need a column with the Value of the Pretest and a column with a Value of the Posttest; following, I would love the query to populate an additional column with the percent change pre-to-posttest.

I am flummoxed and cannot create this dynamic formula.

Please, let me know if there is anything more needed to help with this request.

Desired Data:

ID Pretest Value Posttest Value % Change
101 0.50 0.25 '#0.00'
127 0.13 3.89 '#0.00'
129 2.00 0.62 '#0.00'
130 1.22 0.29 '#0.00'

Raw Data:

ID Test Value
101 Pretest 0.50
101 Posttest 0.25
127 Posttest 3.89
127 Pretest 0.13
129 Pretest 2.00
129 Posttest 0.62
130 Pretest 1.22
130 Posttest 0.29
133 Pretest 0.31
137 Pretest 3.13
138 Posttest 0.63
140 Pretest 1.20
141 Pretest 2.30
100 Posttest 0.75
126 Pretest 1.10
3

1 Answer 1

0

Use query(), like this:

=query( 
  query( 
    Sheet1!A1:C, 
    "select A, max(C) 
     where A is not null 
     group by A 
     pivot B", 
    1 
  ), 
  "select Col1, Col3, Col2 
   where Col2 is not null and Col3 is not null", 
  1 
)

To calculate the diff, put this formula next to the query result table in the same row as the query formula:

=arrayformula( 
  { 
    "Diff"; 
    if(
      C2:C * B2:B, 
      C2:C - B2:B, 
      iferror(1/0) 
    ) 
  } 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.