Interesting Average Scenario

**elfishio** · 08-10-2011, 10:19 AM

I have attached an example file that demonstrates what I am trying to accomplish. My current formula is 7000 characters long, and I want to add more inputs, so I can easily get over the 8000 or so character limit. I am looking for a formula that would scan a matrix for true/false, and average out the data for all those that are true. The problem is that the data is organized by Name, then by year. I have attached the code I use below. This would be a huge help if there could be a formula under 1000 characters that would be expandable for more than 40 possible fields. I also am aware that the "iferror(trends!A1,True)=True" can be replaced with "iserr(trends!a1)" but I am more looking for an array/range function if possible, possibly utilizing a few lookups. Thanks for all the assistance, let me know if there is anything I can clarify

Please Login or Register  to view this content.

**incjourn** · 08-10-2011, 11:09 AM

An array formula may help you. For example:

The following will average all the numbers in cells B1:B5 where cells A1:A5 do not contain an error.
=SUM((ISERROR(A1:A5)=FALSE)*B1:B5)/SUM((ISERROR(A1:A5)=FALSE)*1)

This is an array formula so you should enter it using Ctrl+Shift+Enter.

**elfishio** · 08-10-2011, 01:05 PM

I am more looking for a formula that averages the fields that are designated as True for the given year, for example the first scenario asks to average the data points for B,C and E in 2011, as specified in G11:K13.

**NBVC** · 08-10-2011, 01:15 PM

Try, in G14:

=IF(G$13,AVERAGEIF(INDEX($B$2:$P$2,0,MATCH(G$12,$B$1:$P$1,0)):$P$2,$H$11,INDEX($B3:$P3,0,MATCH(G$12,$B$1:$P$1,0)):$P3),"")

copied down and across in the first table.

You just need to change the $H$11 reference in the 2012 reference to $Q$13 when you put the same formula in P14 and copy down and across.

Is that what you are looking for?