Anyone can optimize these lookup / sumifs ?

[JumboCactuar]

Hi,

i have the following:

[TABLE="width: 697"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Pass Rate[/TD]
[TD]Previous Score[/TD]
[TD]Accumulative Points[/TD]
[TD]Accumulative minus Reduction[/TD]
[TD]Result[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]100.00%[/TD]
[TD]#DIV/0![/TD]
[TD]89[/TD]
[TD]89[/TD]
[TD]PASSED[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]0.00%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]FAILED[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]82[/TD]
[TD]82[/TD]
[TD]PASSED[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]95[/TD]
[TD]95[/TD]
[TD]PASSED[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]0.00%[/TD]
[TD]44[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]FAILED[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]91[/TD]
[TD]91[/TD]
[TD]PASSED[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]88[/TD]
[TD]88[/TD]
[TD]PASSED[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]81[/TD]
[TD]81[/TD]
[TD]PASSED[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]89[/TD]
[TD]89[/TD]
[TD]PASSED[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]100.00%[/TD]
[TD]82[/TD]
[TD]177[/TD]
[TD]167[/TD]
[TD]PASSED[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]50.00%[/TD]
[TD]89[/TD]
[TD]89[/TD]
[TD]146[/TD]
[TD]FAILED[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]50.00%[/TD]
[TD]91[/TD]
[TD]91[/TD]
[TD]156[/TD]
[TD]FAILED[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]82[/TD]
[TD]82[/TD]
[TD]PASSED[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]100.00%[/TD]
[TD]89[/TD]
[TD]182[/TD]
[TD]172[/TD]
[TD]PASSED[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]100.00%[/TD]
[TD]95[/TD]
[TD]185[/TD]
[TD]175[/TD]
[TD]PASSED[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]66.67%[/TD]
[TD]95[/TD]
[TD]177[/TD]
[TD]244[/TD]
[TD]FAILED[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]100.00%[/TD]
[TD]93[/TD]
[TD]268[/TD]
[TD]258[/TD]
[TD]PASSED[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]66.67%[/TD]
[TD]75[/TD]
[TD]180[/TD]
[TD]245[/TD]
[TD]PASSED[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]0.00%[/TD]
[TD]63[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]FAILED[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]


Columns B,C,D and E are all "running values"
Problem i have is when im working with a lot of data (5k rows+), when i input a new value in column A - excel hangs and is slow at calculating. Im guessing because are rely on A values.

Here are the formulas dragged down:

A2 - Input Data

B2

=COUNTIFS($A$2:$A2,A2,$F$2:$F2,"PASSED")/COUNTIF($A$2:$A2,A2)

C2

=IF(A2="",0,IF(ISNA(LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

D2

=IF(A2="",0,SUMIFS(G$2:G2,A$2:A2,A2,F$2:F2,"PASSED"))

E2

=IF(D2=0,0,IF(SUMIF(A$2:A2,A2,G$2:G2)>100,SUMIF(A$2:A2,A2,G$2:G2)-10,SUMIF(A$2:A2,A2,G$2:G2)))

F2

=IF(G2>80,"PASSED","FAILED")

G2 - Input Data


I think the problem lies mainly with the formulas in C, like i dont know if i can check for NA a better way

Appreciate any help

 

[JumboCactuar]

For column C, am I better using something like =IFERROR(anything,0)

 

[Marcelo Branco]

Maybe...

C2 copied down
=IF(A2="",0,IF(ISNA(MATCH(A2,A$1:A1,0)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

M.

 

[JumboCactuar]

Maybe...

C2 copied down
=IF(A2="",0,IF(ISNA(MATCH(A2,A$1:A1,0)),0,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)))

M.

thankyou i will try it

anything for column E? Am i better doing the SUM IF then the deduction in a seperate column?

 

[JumboCactuar]

Bump