Constructing an array reference with CONCATENATE

**lauraleff** · 03-21-2011, 08:24 PM

Hi folks,

I have some source data in a pivot table, and I need to extract the date representing the 90th percentile of the forecasted dates for subsets of the data. Here's the formula as it stands at the moment:

=LARGE(CONCATENATE(ADDRESS(MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)+6,2,,,"Nodes-Forecast"),”:”,ADDRESS(MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)+5,2,,,"Nodes-Forecast")),ROUNDUP(E9*0.9,0))

I'm happy with the LARGE and ROUNDUP functions to figure out the 90th percentile date. The problem is with the CONCATENATE defining the array in the source pivot table, which is giving the "#NAME?" error.

Breaking it down:

* ADDRESS(MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)+6,2,,,"Nodes-Forecast") - Finds the first line that matches the jurisdiction name (C9) (i.e., first cell in the array)

* ,”:”, - This you know, connects the addresses in the CONCATENATE

* ADDRESS(MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)+5,2,,,"Nodes-Forecast") - Finds the subtotal line for the given jurisdiction and figures the cell above it (i.e., last cell in the array)

I can extract the above formulas into individual cells to get the right cell references, and then do a concatenate on those results to get the full array definition. But then using that result (let's say O31) in a LARGE(O31,ROUNDUP(E31*.9,0)) gives a "#VALUE?" error.

Thanks in advance for any thoughts and suggestions.

--LL

**DonkeyOte** · 03-22-2011, 05:27 AM

A few issues

1 - need " " around hyphen rather than ” ”

2 - you don't want the sheet reference in the 2nd ADDRESS call

3 - you would need to encase the CONCATENATE within an INDIRECT call so as to convert the resulting string into a range reference

Result of the above:

=LARGE(INDIRECT(CONCATENATE(ADDRESS(MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)+6,2,,,"Nodes-Forecast"),":",ADDRESS(MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)+5,2))),ROUNDUP(E9*0.9,0))

or:

=LARGE(INDIRECT("'Nodes-Forecast'!R"&MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)+6&"C2:R"&MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)+5&"C2",FALSE),ROUNDUP(E9*0.9,0))
were you to adopt R1C1 notation

However, given use of INDIRECT the above is Volatile.

Unless the target object (Nodes-Forecast in this instance) is variable and/or physically removed & replaced you should (IMO) be looking to use INDEX

=LARGE(INDEX('Nodes-Forecast'!$B$7:$B$500,MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)):INDEX('Nodes-Forecast'!$B$7:$B$500,MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)-1),ROUNDUP(E9*0.9,0))

the above is not volatile per se (only on workbook_open) - for more info. on Volatility see the link in my signature.

**lauraleff** · 03-22-2011, 01:59 PM

Thanks much for the direction. I couldn't use INDEX since that returns the value of the target cell rather than the address. (Also realized I was calling the wrong column.) So here's the formula that worked:

=LARGE(INDIRECT(CONCATENATE(ADDRESS(MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)+6,7,,,"Nodes-Forecast"),":",ADDRESS(MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)+5,7))),ROUNDUP(E9*0.9,0))

Thank you!

--LL

**DonkeyOte** · 03-22-2011, 02:10 PM

Originally Posted by lauraleff

I couldn't use INDEX since that returns the value of the target cell rather than the address.

The above is not correct.

INDEX is a very versatile function and can in fact return either Value or Range Reference depending on context. In this instance we're using in reference form (colon delimiter)

A more basic example:

=SUM(A1:A10)
=SUM(A1:INDEX(A:A,10))
=SUM(INDEX(A:A,1):INDEX(A:A,10))

all of the above do the same thing

I would suggest you test INDEX approach; the below is the earlier suggestion modified per alteration from B to G:

=LARGE(INDEX('Nodes-Forecast'!$G$7:$G$500,MATCH(C9,'Nodes-Forecast'!$B$7:$B$500,0)):INDEX('Nodes-Forecast'!$G$7:$G$500,MATCH(CONCATENATE(C9," Total"),'Nodes-Forecast'!$B$7:$B$500,0)-1),ROUNDUP(E9*0.9,0))