Crating a formula when a value is exceeded

I have a set of values across every month. I need to create a formula that when the criteria is met it tells me what month it was met in. Example. I need to know via a formula when the value exceeds £100.
The figures are jan 25, feb 37, March 58. This means the criteria was met in March. I need to create a formula that will give me that answer (March - in a separate cell) across a large range of data entries for individual customers.

Welcome to the forum!
From your quick description INDEX and MATCH functions might be a good start, could you upload sample spreadsheet?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

See Attachment. I hope it helps

Hi Nick, referring to your sample spreadsheet do you want column N to show which is the first month that exceeds 50?
E.g. N6 should return mar, N7 should return jan, N8 should return feb?

Assume the answer is yes, try this array formula (ctrl+shift+enter) in N6 and copy down till N11:

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

What I want is that each time a comany passes a certain value it tells me which month it has passed that criteria in column N

I'm not getting the logic, could you put up the expected result base on your sample spreadsheet?
N6=?
N7=?
N8=?
N9=?
N10=?
N11=?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Think Alvin's formula does return what you're after. But since you are using Excel 2003, IFERROR does not exist

You could try this rendition in N6, and normal ENTER will do:
=IF(ISNA(MATCH(TRUE,INDEX($B6:$M6>=50,),0)),"",INDEX($B$4:$M$4,1,MATCH(TRUE,INDEX($B6:$M6>=50,),0)))
Copy down

Hi Max

Please see attached spreadsheet. Your assumption is correct but it still didnt like the formula.

Nick

Alvin

I think we are nearly there. The formula you have sent works but only when a £50 value is achieved in a particualr month. I need it to highlight the month that the running value exceeds £50. For instance jan £22, Feb £6, March £37. This means in March the value went past £50 and that is why I would need March highlighted. It could be any month during the year.

Thanks for all of your help so far.

Originally Posted by Max, Singapore

Think Alvin's formula does return what you're after. But since you are using Excel 2003, IFERROR does not exist

You could try this rendition in N6, and normal ENTER will do:
=IF(ISNA(MATCH(TRUE,INDEX($B6:$M6>=50,),0)),"",INDEX($B$4:$M$4,1,MATCH(TRUE,INDEX($B6:$M6>=50,),0)))
Copy down

Hi Nick,

Try this array formula (ctrl+shift+enter) to get month where cumulative value >50.

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Alvin

Thankyou, this has worked!!

Regards

Nick

Thanks for the feedback.Pl mark the thread solved.

Originally Posted by Nick1966

Alvin

Thankyou, this has worked!!

Regards

Nick

Thank you for the feedback, you're welcome



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin