Count Columns until reach target

Hi,

I've got a spreadsheet that looks at the utilization rate of people. I'm interested in looking at the number of months since the new starter joined until they first achieve a 10%, 30%, 50% and 70% utilization rate. Not all new starters joined in the same months. I could manually do this but it is a bit tedious and was wondering if anyone could shed light on a formula.

There is a similar thread that was solved (http://www.excelforum.com/excel-gene...e-reached.html) however it looks at the sum of the product over the months until the break point is reached. I need the number of months until the target utilization rate is met.

I've attached some sample data for reference.
For example, for Person 1 it take them 1, 4, na , na months until they are 10%, 30%, 50%, 70% utilized, respectively. NA indicates they haven't achieved a 50% or 70% utilization rate.
Person 5 takes 2, 2, 5, and 11 months, respectively.

Grateful for any help.

Cheers,
Alex

Originally Posted by alexpickup

For example, for Person 1 it take them 1, 4, na , na months until they are 10%, 30%, 50%, 70% utilized, respectively.

Hi.

Shouldn't it be 6 for 30%?

Regards

Change Cell C1,D1,E1,F1 as 10%,30%,50% and 70% respectively
Try=IFERROR(MATCH(TRUE,INDEX($G2:$AC2>C$1*1,0),0),"na") in Cell C2 and copy across

brilliant, thank's Siva. There is a slight issue as i need to change the range '$G2:$AC2' according to the start date for the individual since the formula doesn't seem to work when it picks up the '-' but i can do this manually. Cheers.