Hi again Nitin,
I updated my mockup formulas to be significantly faster.
I tested with about 13,000 rows and the formulas I suggested yesterday, and it took roughly 10-20 seconds to do a full recalculation of the spreadsheet. Today's formulas--roughly 0.25 seconds !
Please download my sample again (same link) in the event you previously downloaded it.
I was able to speed up the formulas primarily by having them look for dependents in only the next 20 rows (to allow for up to 20 dependents) instead of searching the whole worksheet. I also leveraged the fact that the dependents are listed immediately below the employee in the worksheet, in one contiguous group-- a new formula counts the number of dependents, and the other formulas only search that many rows (and they don't need to check for the employee #, as it's established that these rows are part of the employee record).
I don't know if I took the BEST POSSIBLE strategy for speeding calculation, but the spreadsheet is fast now. If you required tens of thousands of rows, it would still be fast enough on a modern computer. The downside to my strategy is that it relies on the OFFSET() function to identify the dependents based on # of rows below employee. OFFSET is a "volatile" function in Excel, one of several function that cause cells dependent on them to be recalculated every time any change anywhere on the worksheet occurs... This could be avoided by simply modifying yesterday's formulas to search the next 20 rows instead of the whole worksheet, using relative references. I don't know which would perform better overall. The relative references could get messed up with adding and deleting of rows in the spreadsheet, whereas the offset reference will be sure to work as expected. Also, the multiple spouses support in my formulas could be unnecessary
.... I updated my mockup to include a sheet that only supports one spouse, and that sheet should be a little faster. Further speed optimization looks like it would be academic.
The new formulas:
* Number of Dependents (Uses MATCH to find the end of the dependent range. Change the "21" in the formula to the maximum number of dependents plus 1)
F2
Code:
=MATCH($A2,OFFSET($A2,,,21))-1
* Total Premium (Uses SUM to total employee and dependent premiums)
[b]E2 [code]=SUM(OFFSET($D2,,,$F2+1))
* Spouses (Uses COUNTIF to count spouses among dependents)
[b]G2 [code]=IF($F2=0,0,COUNTIF(OFFSET($C2,1,,$F2),"spouse"))
* Spouse Premiums (Uses SUMIF to total spouses premiums)
[b]H2 [code]=IF($F2=0,0,SUMIF(OFFSET($C2,1,,$F2),"spouse",OFFSET($D2,1,,$F2)))
* Children (Number of Dependents - Number of Spouses)
[b]I2[/b] [code]=$F2-$G2
* Child Premiums (Total Premium - Employee Premium - Spouse Premiums)
J2 =$E2-$D2-$H2[/code]
* Recovery Amount (Total Premiums - Employee Premiums - Average of Child Premiums - Average of Spouse Premiums)
L2[/b] =$E2-$D2-IF($I2=0,0,$J2/$I2)-IF($G2=0,0,$H2/$G2)[/code]
* Company Amount (Total Premiums - Recovery Amount)
K2[/b] =$E2-$L2[/code]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For the One Spouse (if any) version, I made the following changes (column letters G:K are equivalent to H:L in original):
* Spouses - column is not needed, removed
* Spouse Premium (Uses VLOOKUP to find the spouse premium)
G2[/b] =IF($F2=0,0,IFERROR(VLOOKUP("spouse",OFFSET($C2:$D2,1,,$F2),COLUMNS($C2:$D2),FALSE),0))[/code]
* Children (Number of Dependents - [1 if any spouse premiums])
H2
Code:
=$F2-($G2<>0)
* Recovery Amount (Total Premiums - Employee Premiums - Average of Child Premiums - Spouse Premium)
K2[/b] [code]=$E2-$D2-IF($H2=0,0,$I2/$H2)-$G2
~~~~~~~~~~~~~~~~~~~~~~~~~
Finally, I adjusted the conditional format.
Originally I used my suggested formula (as for A2):
[code]=A2=A1
with the format:
;;;[/code]
That works great to hide the excess employee numbers, until you delete a row anywhere on the spreadsheet. That breaks the reference in the next row's conditional format rule, causing the employee# on that row to be shown regardless of whether it should or not.
Solution. Keep the "Offset" mood of the day going. Use this formula instead:
=A2=OFFSET(A2,-1,0)[/code]
I hope this is all similar to what you need. If not and/or you need help adapting it, just let me know.
Asa