JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi,
i have the following:
<tbody>
</tbody>
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
C2
D2
E2
F2
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
i have the following:
Name | Pass Rate | Previous Score | Accumulative Points | Accumulative minus Reduction | Result | Value |
John | 100.00% | #DIV/0! | 89 | 89 | PASSED | 89 |
Luke | 0.00% | 0 | 0 | 0 | FAILED | 44 |
George | 100.00% | 0 | 82 | 82 | PASSED | 82 |
Tim | 100.00% | 0 | 95 | 95 | PASSED | 95 |
Luke | 0.00% | 44 | 0 | 0 | FAILED | 63 |
Fred | 100.00% | 0 | 91 | 91 | PASSED | 91 |
Lisa | 100.00% | 0 | 88 | 88 | PASSED | 88 |
Harry | 100.00% | 0 | 81 | 81 | PASSED | 81 |
Paul | 100.00% | 0 | 89 | 89 | PASSED | 89 |
George | 100.00% | 82 | 177 | 167 | PASSED | 95 |
John | 50.00% | 89 | 89 | 146 | FAILED | 67 |
Fred | 50.00% | 91 | 91 | 156 | FAILED | 75 |
Bob | 100.00% | 0 | 82 | 82 | PASSED | 82 |
Paul | 100.00% | 89 | 182 | 172 | PASSED | 93 |
Tim | 100.00% | 95 | 185 | 175 | PASSED | 90 |
George | 66.67% | 95 | 177 | 244 | FAILED | 77 |
Paul | 100.00% | 93 | 268 | 258 | PASSED | 86 |
Fred | 66.67% | 75 | 180 | 245 | PASSED | 89 |
Luke | 0.00% | 63 | 0 | 0 | FAILED | 44 |
<tbody>
</tbody>
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
Code:
=COUNTIFS($A$2:$A2,A2,$F$2:$F2,"PASSED")/COUNTIF($A$2:$A2,A2)
C2
Code:
=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
Code:
=IF(A2="",0,SUMIFS(G$2:G2,A$2:A2,A2,F$2:F2,"PASSED"))
E2
Code:
=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
Code:
=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