Average array, 1 row condition, 2 column conditions

[Agrippa123]

Hi all,

I would like a formula which calculates the average according to three conditions.

As an example, this is what the data looks like (I couldn't attach the excel file for some reason)

Please Login or Register  to view this content.

In this case, I'd like to average according to the conditions;
-Asia
-One
-Small

Does anyone know how to do this? I actual dataset is much bigger so an automatic way of doing this is what I'm after.

Thanks

[samba_ravi]

please attach a sample excel file

[Agrippa123]

Hi, thanks for replying.

However, I don't see an attachment in your post..

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[JohnTopley]

One way ...

=SUMPRODUCT(($B$2:$E$7)*($A$2:$A$7="Asia")*($B$1:$E$1="One Small"))/SUMPRODUCT(($A$2:$A$7="Asia")*($B$1:$E$1="One Small"))

Better if you put the criteria is separate celLs

=SUMPRODUCT(($B$2:$E$7)*($A$2:$A$7=M1)*($B$1:$E$1=N1))/SUMPRODUCT(($A$2:$A$7=M1)*($B$1:$E$1=N1))


M1 ="Asia"

N1 ="One small"

[Agrippa123]

OK - so I hope the excel file is attached with a before and an after tab - is it clearer now?

[NeedForExcel]

Put this in C4 of the After sheet

=INDEX(Before!$C$5:$F$10,MATCH(C$3,Before!$B$5:$B$10,0),MATCH(1,($B4=Before!$C$3:$F$3)*($A4=Before!$C$4:$F$4),0))

Drag down and across

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Agrippa123]

Hi NeedForExcel,

Thanks for your reply. I got the formula you provided working, but it picks one cell value.

What I would like is the average of all the cells which meet the three predefined conditions.

Thanks

[Agrippa123]

To clarify, in the case of Asia-Pacific, it appears twice in the "Before" tab. Therefore the output should be an average of where ever "Asia-Pacific", "One" and "Small" occur.

[JohnTopley]

See attached: why make life difficult using two columns/rows when one would do?

=SUMPRODUCT((Before!$C$5:$F$10)*(Before!$B$5:$B$10=After!C$3)*(Before!$C$4:$F$4=After!$B4))/SUMPRODUCT((Before!$B$5:$B$10=After!C$3)*(Before!$C$4:$F$4=After!$B4))

[Phuocam]

Try this in cell C4:

=AVERAGE(IF(C$3=Before!$B$5:$B$10,OFFSET(Before!$B$5:$B$10,0,MATCH($B4&$A4,Before!$C$3:$F$3&Before!$C$4:$F$4,0)),""))

Enter with Ctrl+Shift+Enter

Copy across and down

[Agrippa123]

Phuocam, it works!!!!! THANKS SO MUCH!

[Phuocam]

You're welcome.