Exclude blank/FALSE cells in in Excel array IF formula output

I am having difficulties with making an array formula work the way I want it to work.

Out of a column of dates which is not sorted, I want it to extract values into a new column. The formula below identifies the required cells of a given month and year, but they appear in their original row rather than on top of the output range. Moreover, I want all ""/FALSE cells to be excluded from the output array.

Please Login or Register  to view this content.

In fact, the $I$15:$I$1346 should be dynamic and go to the last filled range (I could make a named range for that)

Part two is to expand on that formula so that it calculates the data that is an two column offset of the data described above.

Is the above possible to build into one cell probably with a combination of IF, INDEX, SMALL and maybe others?

I'm not looking for a filter solution. Hope the above is clear enough and that you can help!

Here's a shortened sample layout:

x A B C
1 Date Series_A Series_B
2 03/01/2011 45 20
3 04/01/2011 73 30
4 06/01/2011 95 40
5 08/01/2011 72 50
6 06/02/2011 5 13
7 09/02/2011 12 #N/A
8 05/02/2011 23 65
9 07/03/2011 12 65

Then I want three input cells for the year and and the month and series name (index/match, as there are many more columns with data). If it would be 2011, Feb and Series_A, I want it to calculate the average for that month. In this case it would be (5+12+23)/3. If it would be Feb-2011 and Series_B instead, which has an error, it should show (13+65)/2 rather than an error.

Aside from that I want a separate which will output an array with the data instead without 'holes' in between and with the right 'length'. Example for Feb-2011 in Column C:
x A B C D
1 Date Series_A Desired Output Output based on f above
2 03/01/2011 45 5
3 04/01/2011 73 12
4 06/01/2011 95 23
5 08/01/2011 72
6 06/02/2011 5 5
7 09/02/2011 12 12
8 05/02/2011 23 23
9 07/03/2011 12

If if then run a =ISBLANK(C5) it should be true, rather than =""=C5

Hello all,

I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.

Lets say I have cell (A1 for example) ='2013'!A39

Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on

I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39

Is this possible?

Thank you!

Originally Posted by arlu1201

Daniel,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Sorry, I thought I had created a new thread. This was just clumsy...

Thanks, it's a step on the way but no exactly what i need. The reason why I want it to expand to a range is that I want to be able to run min/max/averages on it too (ie full formula in one cell).

Is it possible?

Another thing is that if I set January 2008 and series B, it will skip the #n/a but also fail to include cells b18:b22

Let me know

I think that does the trick for te avg min etc. Will let you know soon. Still, the array constructed list would be of interest. Just can't wrap my head around it

Thanks