1

Here is my dataset :

Dataset

I have many Tests (Test 1 and Test 2), and, for every test, 4 conditions (C1,C2, C3 and C4). I am looking for a solution to select the datas from the tests by conditions, on Excel. So here selecting every fourth columns.

(Here it will be easy to select with Crtl but my real dataset had 40 tests and approximately 100 values by columns)

I've search and tried many solutions found on the Internet (like : How to average every nth column in Excel? ) but it works only with one value by rows. Here I want the complete columns.

I also tried a solution with VBA on Excel ( here : https://trumpexcel.com/select-every-third-row/ ) but it will only select, I cannot put the selection into a formula to have the mean, the std deviation, etc.

So, it is possible to do what I need ? Or have I to reorganize my data manually ?

MathB
  • 49
  • 9
  • 1
    Assuming that data is in columns A:H, array formula: `=AVERAGE(IF($A$2:$H$2="C1",$A$3:$H$100))` for example. – Rory Nov 23 '20 at 16:09
  • @Rory Thanks. I test it but it take all the columns. The condition (="C1") is only verified on the first case (A2). If A2 = "C1" : the average is on everything. If A2 != "C1" : average = 0. – MathB Nov 23 '20 at 16:18
  • 1
    Then you didn't array enter the formula with Ctrl+Shift+Enter. – Rory Nov 23 '20 at 16:24
  • @Rory Ok, I'm a real noob on Excel. It works perfectly, thanks a lot ! – MathB Nov 23 '20 at 16:29
  • 1
    No worries - I've added that as an answer with the clarification of using those keys to enter it. :) – Rory Nov 23 '20 at 17:31

1 Answers1

4

Assuming that data is in columns A:H, array formula:

=AVERAGE(IF($A$2:$H$2="C1",$A$3:$H$100))

which must be entered using Ctrl+Shift+Enter, not just Enter, for example.

Rory
  • 32,730
  • 5
  • 32
  • 35
  • Thanks a lot ! Here if I want to exclude the values equal to zero (not in my example), how can I do it ? (I tried to use a formula like `=AVERAGEIF(IF($A$2:$H$2="C1",$A$3:$H$100);"<>0")` but it doesn't work :/ Edit : After many tests, your formula seems to already exclude the zeros, is that true ? – MathB Nov 24 '20 at 10:42
  • 1
    No, if you need to exclude 0s (and blank cells), use: `=AVERAGE(IF($A$2:$H$2="C1",IF($A$3:$H$100<>0,$A$3:$H$100)))` – Rory Nov 24 '20 at 12:30
  • Ok thanks! It was a bit strange because my average doesn't change when I delete some 0s for test with the first formula. But I will adopt your second one (It gives me a different result, even if the two formulas seems to react the same, I admit that certainly not !) – MathB Nov 24 '20 at 13:05
  • 1
    The first one would treat blank cells as 0, so the result wouldn't change if you just deleted a 0. – Rory Nov 24 '20 at 14:18
  • Oh ok I see. Glad to better understand how Excel works. Have a great day ! – MathB Nov 24 '20 at 14:25