Quick/Simple problem: Average function based on filtering rows? (please help!)

A picture is not much use - attach the sample workbook instead, as contributors are unlikely to spend a lot of time to re-create your sheet.

Hope this helps.

Pete

OK thanks Pete! I believe I've now successfully attached it after editing my opening post - though I've also hosted it on an external site just in case.
http://www.filedropper.com/book2_4 (download workbook)

I'm a bit confused as to what you want to do. In your first post you gave an example of entering 2 into cell A32, but the values in A1:A30 seem to be the equivalent of the row (or record) number. I think what you mean is that you want to be able to enter a value into one of the cells (only) in B32 to M32, and then to have B33:M33 display the average of each column where the values in the selected column are equal to the chosen value. If so, you can put this formula in B33:

=IF(OR(SUM($B$32:$M$32)=0,COUNT($B$32:$M$32)>1),0,AVERAGEIFS(B$1:B$30,INDEX($B$1:$M$30,,MATCH(SUM($B$32:$M$32),$B$32:$M$32,0)),SUM($B$32:$M$32)))

and copy across to M33. Then you can enter any value in one of the cells in B33:M33 and get the average for each column. You will get zeros if you have more than one cell with a value, or if they are all empty.

Hope this helps.

Pete

I think what you mean is that you want to be able to enter a value into one of the cells (only) in B32 to M32, and then to have B33:M33 display the average of each column where the values in the selected column are equal to the chosen value.

Many thanks Pete! That works perfect for entering into one cell only, but I would be really grateful if you could get it to work based on multiple values in row 32? Apologies for not making it clear!

I'm going out soon, so I don't have time to think about that further requirement - perhaps someone else will chip in.

Pete

Hi PCI,

Thanks for also taking a look at this! Yours works as good as Pete's and uses a different method, so if I enter 2 in B32 it will select the rows with value 2 and then figure out the conditional averages of each column:

Please Login or Register  to view this content.

Averages are:

Please Login or Register  to view this content.

So that works great, but there's 1 problem and 1 additional feature I would be most grateful to have included.


Problem is if I enter 0 in E32:

It should select these rows during the calculation:

Please Login or Register  to view this content.

But it just comes up as invalid for the averages, as I think the validation doesn't factor in zeroes.


Now the additional feature I would love to have would involve multiple criteria for the conditional averages:

If I enter 2 in B32 and 0 in E32, it should select just one row and return that same row as the average since there are no other rows to compare in this instance:

Please Login or Register  to view this content.

I realise this may involve some kind of complex nested formula or possibly VBA code, but I'll happily pay $20 or more if somebody can implement it in full?

Yeah, column A can be ignored.

Could you possibly attempt it on this new attached spreadsheet please, if you get a chance, but in the space at the top?

WARRIORS.zip

That seems perfect!!! MANY THANKS INDEED, CHEERS! Drop me a pm with your PayPal addy if you would like me to make a donation for your efforts. I'm sure Pete would have come up with a solution too - it's just I wasn't very good at explaining things in the beginning.

THANKS AGAIN PCI!