Average of cells in colum C for rows which have a combination of values in colum A and B

[Petrichor]

Hello agian, I'm full of questions it seems

I am wondering is there is a way to tell Excel to produce an average of certain column's cell values for rows which have a combination of values in column A and B.

Please see excel file attached.

I need an average of column's N cells for rows 1 to 9 because those cells all have 'Trial001' as a value in column A and P01 as a value in column B. I need averages of all unique combinations of values in column A and B.

That is, I need an average of the cells in column N for rows which have Trial002 and P01, Trial003 and P01 and so on for all 75 trials.
Then I need an average of all cells in column N for rows which have Trial001 and P02Pilot, Trial002 and P02Pilot and so on for all the trials.
Then same for P02Pilot and so on for every value in column B (in this sheet goes until P18).


I understand that I can do this manually by applying AVERAGE function, but I have many sheets like that and it would be very time consuming. Is there a way to tell excel to do this automatically for all rows with a unique combnation of values in columns A and B? That would save me weeks of work..weeks I really need!

Thank you all kind folk!

[Glenn Kennedy]

Use AVERAGEIFS
=AVERAGEIFS(N:N,A:A,P2,B:B,Q2)

as shown in the sheet.

[Glenn Kennedy]

Revised results layout.

[southward]

Hi,

You can use a Pivot Table.

Attached is a version of your file with a Pivot Table.

Hope this is helpful.

Cheers

[Petrichor]

Southward,

what you've done is increadible, that is exactly what I need! However, I am not able to replicate this

I tried selecting just one row before clicking on insert Pivot and tried selecting all three columns and the whole worksheet, but whichever way I try I keep on getting an error message, eg. one attached in a printscreen.

Do you have any idea what am I doing wrong? Could you please tell me step by step like you would for a granny who has never used computers :D

[Petrichor]

Awesome! Thank you soo much

Just again, I need to take some time to try and figure out how did you achieve this, as I am struggling to get my head around the functions you use and so I am nnot sure how to apply this to a new data set. That is not to say you are not being clear, I am just an extreme dummy with excel.

I will be back with questions after work :D

[Petrichor]

Glenn!

That's awesome! Thank you soo much

Just again, I need to take some time to try and figure out how did you achieve this, as I am struggling to get my head around the functions you use and so I am nnot sure how to apply this to a new data set. That is not to say you are not being clear, I am just an extreme dummy with excel.

I will be back with questions after work :D

[Glenn Kennedy]

I'm usually here for a while, most days: so ask away...

Thanks for the Rep, BtW.

[southward]

Hi Petrichor,

Sorry, I neglected to indicated the requirements for inserting a Pivot Table. All the columns have to have something. In your situation, I had to give all the columns between B and N a header.
In the example I posted I just entered a letter as a header for all those in between.
This acts as a bridge/connector to associate column A&B with column N so that Excel will work the data as a group.

Cheers

[Petrichor]

Southward,

ok, so I have copied the values in column N to column C to not have columns in between. But I am not sure how to select function 'Average of Values' in the Values Box on the right? I automatically get 'Count of Values' chosen by default and do not seem to be able to cose any other function?

See Attached.

Thank you!! And sorry I am so useless at this. I should have went to some Ecxel courses clearly..

[Petrichor]

No, hold on, I worked it out :D So sorry and so thank you haha!

Have a great day!

[Glenn Kennedy]

Not sure who you're talking to...

But.

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as Solved.

[Petrichor]

Glenn,

sorry first I was talking to Southward. I still need to see if I am able to replicate Your solution, because it might actually be a more useful data layout for me. Will try taht shortly and once it Works will mark the thread as Solved.

Also, sorry I did not realise I can add reputation after every single post, will do that!

[Petrichor]

Glenn,

so as I supected I am not capable to produce the results layout with the formula you gave. I am sure it is something very basic that I don't get and that is why innitiation of the formula is not allowed. This is what happens if I try:

[Petrichor]

Sorry that will not work as the image is not clear enough. Basically the error Message tells me that there is a problem with teh formula and Excel seems to think that I am not actually wanting to use a formula and therefore suggests that I type '=AVERAGEIFS(N:N,A:A,P2,B:B,Q2) because ' will make it clear that it is not a formula but a value. But I need a formula :/

[Glenn Kennedy]

The problem is that you're Norwegian. Replace , with ; also: you have no value in Q2 and P2. Better to open the sheet at Post 3 and work from there.