finding the second last date

[ceemo]

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

={max(if(col B="Bob", 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

[Peo Sjoblom]

Not really tested

=LARGE(IF(B2:B8="Bob", IF(C2:C8=1, A2:A8)),2)

also array entered, adapt to fit

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"ceemo" <ceemo.26j2my_1145477701.3503@excelforum-nospam.com> wrote in
message news:ceemo.26j2my_1145477701.3503@excelforum-nospam.com...
>
> i am using the following formula to find the last date bob completed his
> work 100%
>
> ={max(if(col B="Bob", 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:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=534334
>

[ceemo]

thank you for your help

[ceemo]

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="Bob", IF(C2:C8=1, A2:A8)),2)

Can u help ?

[Domenic]

Try...

=INDEX(D2:D8,LARGE(IF(B2:B8="Bob",IF(C2:C8=1,ROW(D2:D8)-ROW(D2)+1)),2))

....confirmed with CONTROL+SHIFT+ENTER .

Hope this helps!

In article <ceemo.26krim_1145556601.2379@excelforum-nospam.com>,
ceemo <ceemo.26krim_1145556601.2379@excelforum-nospam.com> wrote:

> 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="Bob", IF(C2:C8=1, A2:A8)),2)
>
> Can u help ?