Find the first occurence of a value in a column

melwin209

New Member
Joined
Sep 26, 2018
Messages
4
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.

IterationMeasureLoad
(=(B2+B3)/2)

<tbody>
</tbody>
Pass/Fail
10.002FALSE
10.300FALSE
10.600FALSE
11.199FALSE
11.399FALSE
11.599TRUE
11.799TRUE
12.000TRUE
12.200TRUE
13.000TRUE
19.999FALSE
20.002FALSE
20.300FALSE
20.600FALSE
21.199FALSE
21.399TRUE
21.599TRUE
21.799TRUE
22.000TRUE
22.200TRUE
23.000FALSE
29.999FALSE

<tbody>
</tbody>

Regards,
Melwin
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

melwin209

New Member
Joined
Sep 26, 2018
Messages
4
Thank you for your response Aladin Akyurek. I have updated it!


IterationMeasureLoad
(=(B2+B3)/2)

<tbody>
</tbody>

Pass/Fail
10.002
0.151005

<tbody>
</tbody>
FALSE
10.300
0.449508

<tbody>
</tbody>
FALSE
10.600
0.899302

<tbody>
</tbody>
FALSE
11.199
1.2992

<tbody>
</tbody>
FALSE
11.399
1.49925

<tbody>
</tbody>
FALSE
11.599
1.69915

<tbody>
</tbody>
TRUE
11.799
1.89955

<tbody>
</tbody>
TRUE
12.000
2.09995

<tbody>
</tbody>
TRUE
12.200
2.59985

<tbody>
</tbody>
TRUE
13.000
6.4995

<tbody>
</tbody>
TRUE
19.999
5.000781

<tbody>
</tbody>
FALSE
20.002
0.150957

<tbody>
</tbody>
FALSE
20.300
0.44962

<tbody>
</tbody>
FALSE
20.600
0.899494

<tbody>
</tbody>
FALSE
21.199
1.2993

<tbody>
</tbody>
FALSE
21.399
1.49935

<tbody>
</tbody>
TRUE
21.599
1.6993

<tbody>
</tbody>
TRUE
21.799
1.8996

<tbody>
</tbody>
TRUE
22.000
2.10005

<tbody>
</tbody>
TRUE
22.200
2.6

<tbody>
</tbody>
TRUE
23.000
6.4996

<tbody>
</tbody>
FALSE
29.999
5.000877

<tbody>
</tbody>
FALSE

<tbody>
</tbody>

<tbody>
</tbody>



<tbody>
</tbody>
 

melwin209

New Member
Joined
Sep 26, 2018
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

New Member
Joined
Sep 26, 2018
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,011
Messages
5,526,263
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top