CODEWITHSUNDEEP
excelconditional-statementsaveragemultiple-columnsselection
MathB
MathB

Reputation: 49

Excel (Average, Std déviation, ...) - with values every Nth columns

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 ?

Upvotes: 1

Views: 162

Answers (1)

Rory
Rory

Reputation: 34045

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.

Upvotes: 4

Related Questions