software

i am using the following formula to find the last date bob completed his
work 100%

={max(if(col B=quot;Bobquot;, if(col C=1, Col A)))}

Date Name complete%
28/1/6 bob 100%
31/1/6 bob 100%
1/2/6 bob 100%
1/2/6 pete 100%
2/2/6 bob 65%
2/2/6 steff 89%
3/2/6 bob 100%

i would like to amend this formula to find the second to last date bob
completed 100% which would be the 1/2/6--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534334Not really tested

=LARGE(IF(B2:B8=quot;Bobquot;, IF(C2:C8=1, A2:A8)),2)

also array entered, adapt to fit

--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;ceemoquot; gt; wrote in
message ...
gt;
gt; i am using the following formula to find the last date bob completed his
gt; work 100%
gt;
gt; ={max(if(col B=quot;Bobquot;, if(col C=1, Col A)))}
gt;
gt; Date Name complete%
gt; 28/1/6 bob 100%
gt; 31/1/6 bob 100%
gt; 1/2/6 bob 100%
gt; 1/2/6 pete 100%
gt; 2/2/6 bob 65%
gt; 2/2/6 steff 89%
gt; 3/2/6 bob 100%
gt;
gt; i would like to amend this formula to find the second to last date bob
gt; completed 100% which would be the 1/2/6
gt;
gt;
gt; --
gt; ceemo
gt; ------------------------------------------------------------------------
gt; ceemo's Profile:
gt; www.excelforum.com/member.php...oamp;userid=10650
gt; View this thread: www.excelforum.com/showthread...hreadid=534334
gt;

thank you for your help--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534334
ColA ColB ColC ColD
Date Name complete% Grade A
28/1/6 bob 100% A
31/1/6 bob 100% G
1/2/6 bob 100% C
1/2/6 pete 100% E
2/2/6 bob 65% C
2/2/6 steff 89% C
3/2/6 bob 100% B

I have the below formula to get the second to last date that Bob got
100% but i would like to get the grade (C) that Bob got on th second to
last time he got 100%

=LARGE(IF(B2:B8=quot;Bobquot;, IF(C2:C8=1, A2:A8)),2)

Can u help ?--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534334Try...

=INDEX(D28,LARGE(IF(B2:B8=quot;Bobquot;,IF(C2:C8=1,ROW(D 28)-ROW(D2) 1)),2))

....confirmed with CONTROL SHIFT ENTER .

Hope this helps!

In article gt;,
ceemo gt; wrote:

gt; ColA ColB ColC ColD
gt; Date Name complete% Grade A
gt; 28/1/6 bob 100% A
gt; 31/1/6 bob 100% G
gt; 1/2/6 bob 100% C
gt; 1/2/6 pete 100% E
gt; 2/2/6 bob 65% C
gt; 2/2/6 steff 89% C
gt; 3/2/6 bob 100% B
gt;
gt; I have the below formula to get the second to last date that Bob got
gt; 100% but i would like to get the grade (C) that Bob got on th second to
gt; last time he got 100%
gt;
gt; =LARGE(IF(B2:B8=quot;Bobquot;, IF(C2:C8=1, A2:A8)),2)
gt;
gt; Can u help ?