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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Iteration[/TD]
[TD]Measure[/TD]
[TD]Load[TABLE="width: 71"]
<tbody>[TR]
[TD="class: xl65, width: 71"](=(B2+B3)/2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Pass/Fail[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.002[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.300[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.600[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.199[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.399[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.599[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.799[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2.000[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2.200[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3.000[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9.999[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.002[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.300[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.600[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.199[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.399[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.599[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.799[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.000[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.200[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.000[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9.999[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

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!


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Iteration[/TD]
[TD]Measure[/TD]
[TD]Load
[TABLE="class: cms_table, width: 71"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 71"](=(B2+B3)/2)[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]Pass/Fail[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.002[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]0.151005[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.300[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]0.449508[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.600[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]0.899302[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.199[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]1.2992[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.399[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]1.49925[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.599[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]1.69915[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.799[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]1.89955[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2.000[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2.09995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2.200[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]2.59985[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3.000[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]6.4995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9.999[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]5.000781[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.002[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]0.150957[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.300[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]0.44962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.600[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]0.899494[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.199[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]1.2993[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.399[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]1.49935[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.599[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]1.6993[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.799[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]1.8996[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.000[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]2.10005[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.200[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]2.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.000[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]6.4996[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9.999[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5.000877[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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