Find the first occurence of a value in a column

[melwin209]

Hi,

Can somebody please help me out here.

When the Pass/Fail becomes True, only at that point I want to take the average value in the third column(Load (=(B2+B3)/2)). But I only want to take the average when the pass/fail becomes TRUE in the first instance for each iteration(in this case - row 7) not for all the TRUE cases(These cases can be set as N/A including the FALSE cases) . Here i have pasted the data for just 2 iterations, but i will be doing it for more. How can i do this?

Appreciate your help! Thank you.

Iteration	Measure	Load (=(B2+B3)/2) <tbody> </tbody>	Pass/Fail
1	0.002		FALSE
1	0.300		FALSE
1	0.600		FALSE
1	1.199		FALSE
1	1.399		FALSE
1	1.599		TRUE
1	1.799		TRUE
1	2.000		TRUE
1	2.200		TRUE
1	3.000		TRUE
1	9.999		FALSE
2	0.002		FALSE
2	0.300		FALSE
2	0.600		FALSE
2	1.199		FALSE
2	1.399		TRUE
2	1.599		TRUE
2	1.799		TRUE
2	2.000		TRUE
2	2.200		TRUE
2	3.000		FALSE
2	9.999		FALSE

<tbody>
</tbody>

Regards,
Melwin

 

[Aladin Akyurek]

Care to post the averages which must obtain for the sample you posted?

 

[melwin209]

Thank you for your response Aladin Akyurek. I have updated it!

Iteration Measure Load (=(B2+B3)/2) <tbody> </tbody> Pass/Fail 1 0.002 0.151005 <tbody> </tbody> FALSE 1 0.300 0.449508 <tbody> </tbody> FALSE 1 0.600 0.899302 <tbody> </tbody> FALSE 1 1.199 1.2992 <tbody> </tbody> FALSE 1 1.399 1.49925 <tbody> </tbody> FALSE 1 1.599 1.69915 <tbody> </tbody> TRUE 1 1.799 1.89955 <tbody> </tbody> TRUE 1 2.000 2.09995 <tbody> </tbody> TRUE 1 2.200 2.59985 <tbody> </tbody> TRUE 1 3.000 6.4995 <tbody> </tbody> TRUE 1 9.999 5.000781 <tbody> </tbody> FALSE 2 0.002 0.150957 <tbody> </tbody> FALSE 2 0.300 0.44962 <tbody> </tbody> FALSE 2 0.600 0.899494 <tbody> </tbody> FALSE 2 1.199 1.2993 <tbody> </tbody> FALSE 2 1.399 1.49935 <tbody> </tbody> TRUE 2 1.599 1.6993 <tbody> </tbody> TRUE 2 1.799 1.8996 <tbody> </tbody> TRUE 2 2.000 2.10005 <tbody> </tbody> TRUE 2 2.200 2.6 <tbody> </tbody> TRUE 2 3.000 6.4996 <tbody> </tbody> FALSE 2 9.999 5.000877 <tbody> </tbody> FALSE <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[melwin209]

Hi,

I was able to solve it. Turns out the solution was simple.

I was using the IF & AND condition, but I wasn't getting the expected result.

=IF(AND(D1="FALSE",D2="TRUE"),(B2+B3)/2,"N/A")

I changed it to

=IF(AND(D1=FALSE,D2=TRUE),(B2+B3)/2,"N/A")

Regards,
Melwin

 

[Aladin Akyurek]

Congrats, that's great. However, I can't comment on whether the solution is correct or not as the averages which must obtain are not available.

 

[melwin209]

Congrats, that's great. However, I can't comment on whether the solution is correct or not as the averages which must obtain are not available.

Hi Aladin Akyurek, Thank you. I did post the averages in a new excel fine and it was on wait for the approval from the admin. And before they could approve it, I was able to figure it out.

Anyways thank you and have a nice day!

Melwin