return column heading when sum of row values exceeds target

[paul100]

Hi

im trying to develop a formula to identify when a budget has been exceeded. to simplify;

B10:G10 contains Jan, Feb, Mar, Apr, May, Jun
B11:G11 contains 1, 2, 5, 7, 9, 5

A11 contains the budget, 7

I need the output in H11 to indicate "Mar" representing the month where the budget value was exceeded.

I have tried to modify numerous array formulas, but with no success. Any ideas?

thanks

 

[AlphaFrog]

Try this array formula.

=IF(MAX(B11:G11)>A11,INDEX(B10:G10,MATCH(1,IF(B11:G11>A11,1,0),0)),"")

 

[Peter_SSs]

B10:G10 contains Jan, Feb, Mar, Apr, May, Jun
B11:G11 contains 1, 2, 5, 7, 9, 5

A11 contains the budget, 7

I need the output in H11 to indicate "Mar" representing the month where the budget value was exceeded.

Like this?

Book1
	A	B	C	D	E	F	G	H
10		Jan	Feb	Mar	Apr	May	Jun
11	7	1	2	5	7	9	5	Mar
Exceed Target

Cell Formulas
Range	Formula
H11	=INDEX(B10:G10,MATCH(TRUE,INDEX(SUMIF(OFFSET($B$11,,,1,COLUMN(B11:G11)-COLUMN(B11)+1),"<>")>A11,,0),0))

 

[paul100]

excellent. thanks peter..

 

[Peter_SSs]

excellent. thanks peter..

You are welcome. Glad it was what you wanted.