Index Match Sum problem (possible array formula)

[danl1988]

Hi folks,

I have data with a number of dates and percentages and I'm trying to find the date where the sum of the various percentages passes a certain threshold.

For a very simplified example, suppose my data looks like this:

• Row 1 (from A1:Z1) are month labels from 1 to 26.
• Row 2 (from A2 to Z2) are percentages. For simplicity, suppose every value is 25%.

My threshold is an input, but let's say it's set to 100%. I'm looking to return the month label where the sum of percentages first exceeds 100%. In this case, that'd be month 5 since the total at this point would be 125%.

In the past I've used something like the following to return the first individual value that exceeded 100%: =INDEX(A1:Z1,MATCH(TRUE,INDEX(A2:Z2>100%,0),))

However, this time around I need to return the month label where the running total of percentages in row 2 exceeds 100% and I'm having a bit of difficulty incorporating the SUM element.

I have many lines of data so it's not particularly practical to build a separate sum sheet. I'm really looking for it all to be in a single formula.

Thanks,

Dan

 

[Andrew Fergus]

Hi Dan
To do this would require a loop inside a formula which Excel cannot do*. Your options are to either have a user defined function using VBA (not recommended) or using a helper row to calculate a running total in a spare row on the sheet (recommended), and then you can use a single formula - I realise the helper row defeats the purpose of a single cell formula but thems the breaks!
Andrew

*clarification: Excel *can* do this but it requires a self referencing formula that resolves to a circular reference which is not for the faint hearted!

 

[Special-K99]

Ignore this, my solution didnt work

 

[Aladin Akyurek]

Given the data in A1:Z2 and the target percentage in A5, in B5 control+shift+enter, not just enter:

=INDEX(A1:Z1,MIN(IF(SUBTOTAL(9,OFFSET(A2,0,0,1,COLUMN(A2:Z2)-COLUMN(A2)+1))>=A5,COLUMN(A1:Z1)-COLUMN(A1)+1)))

You might want to adjust the >= test to just the > test.

 

[Andrew Fergus]

Hi Aladin
Nice use of the SUBTOTAL & OFFSET - I had been trying to do this with INDEX, SUM & OFFSET using MIN(IF(SUM(A2:OFFSET(A2,0,COLUMN(A1:Z1)....etc) without the SUBTOTAL and was pulling my hair out getting the formula to recognise the array! I will have to remember this technique.
Andrew

 

[danl1988]

Thank you very much Aladin. This accomplishes precisely what I was looking for.

 

[Aladin Akyurek]

Thank you very much Aladin. This accomplishes precisely what I was looking for.

You are welcome.