Calculating 8 Threads (Inefficient Code)

[Fugdkn]

As of current, the excel workbook is fine, but when I start adding more columns, that's when issues started to arise. Basically, every Week (the week I have shown is Week 28) will be copied an pasted until we reach Week 52. The same is true for both sheets. The original "Reps (Per Week)" sheet actually is supposed to have loads of stuff in there per Week, but I had to get rid of that due to sensitive information, but it would have been say 15 columns per week, each involving multiple calculations in a given cell, some of which were quite complex (it shouldn't matter too much though because that sheet always works at a good speed, even with 30+ weeks, that is to say, it was only when I added the Reps Adjustment sheet that I started to get problems).

The labels may not make as much sense as they should ideally, but once again, there was sensitive info (for the company I work at). Basically, I just need to know what part of the code in my "Reps Adjustment Sheet" is likely causing issues or volatility.

I should note that I actually changed one aspect of it already, so maybe it's already fixed, but basically I switched out a column that had "=SUMIF($B:$B,E4,J:J)" for each row (with E4 adjusting as it would) for a pivot table because I realised that a pivot table made more sense.

I hope what I have said makes sense. Basically, the "Reps per Adjustment Sheet is causing major slowdown issues when multiple "weeks" are made (looking at the file I have attached should make things clearer).

EDIT - The Reps Adjustment Sheet is basically taking a set of exercises (for a given bodypart) and ensuring that any bodypart that hasn't been given enough reps (1008), has a sufficient amount of reps added to it's exercises, in order of the "usefulness" of an exercise (basically, exercises that are higher in the list are seen as a better, so if a "body part" has two exercises, but they fall short of 1008 by 200, then 2/3 of the 200 should be allocated to the first (best) exercise, and 1/3 of the 200 should be allocated to the second (not as good) exercise (everything is rounded to meet multiples of 4). Really sorry for how weird the example seems. I am just trying to hide sensitive info. If this doesn't make enough sense, then I will rewrite it so that it can be understood more easily.

[Greg M]

Hi there,

Take a look at the attached version of your workbook and see if it gets you moving in the right direction.

A few points:

I'm operating with Excel 2013, and therefore don't have access to the XLOOKUP function, so I've used the old-fashioned INDEX and MATCH approach;

you are applying the XLOOKUP and COUNTIF functions to entire rows (16,384 cells) and columns (1,048,576 cells) - i.e. HUGE numbers of cells, so you can certainly expect performance to take a fairly sizeable hit in such cases;

I've applied my formulas to 26 columns (A:Z) and 500 rows (1:500) for demonstration purposes. You will probably need to increase the number or columns to suit your eventual requirements, but the number of rows might be sufficient;

the array formulas which you use in column M appear slightly complicated, so I did not bother trying to convert them, and just copied the cell VALUES from your original workbook. Obviously you will need to replace these values with appropriate formulas.

Typical formula conversions are as follows:

Reps Adjustment Sheet, Cell C4:

=_xlfn.XLOOKUP(C$2,'Reps (Per Week)'!$1:$1,'Reps (Per Week)'!8:8)

was replaced with:

=INDEX('Reps (Per Week)'!A8:Z8,  MATCH(C$2,  'Reps (Per Week)'!A$1:Z$1,  0))

Reps Adjustment Sheet, Cell G4:

=IF(E4="NONE",0,COUNTIF(I:I,E4))

was replaced with:

=IF(E4 = "NONE",  0,  COUNTIF(I$1:I$500,  E4))

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Fugdkn]

Hi there,

Take a look at the attached version of your workbook and see if it gets you moving in the right direction.

A few points:

I'm operating with Excel 2013, and therefore don't have access to the XLOOKUP function, so I've used the old-fashioned INDEX and MATCH approach;

you are applying the XLOOKUP and COUNTIF functions to entire rows (16,384 cells) and columns (1,048,576 cells) - i.e. HUGE numbers of cells, so you can certainly expect performance to take a fairly sizeable hit in such cases;

I've applied my formulas to 26 columns (A:Z) and 500 rows (1:500) for demonstration purposes. You will probably need to increase the number or columns to suit your eventual requirements, but the number of rows might be sufficient;

the array formulas which you use in column M appear slightly complicated, so I did not bother trying to convert them, and just copied the cell VALUES from your original workbook. Obviously you will need to replace these values with appropriate formulas.

Typical formula conversions are as follows:

Reps Adjustment Sheet, Cell C4:

=_xlfn.XLOOKUP(C$2,'Reps (Per Week)'!$1:$1,'Reps (Per Week)'!8:8)

was replaced with:

=INDEX('Reps (Per Week)'!A8:Z8,  MATCH(C$2,  'Reps (Per Week)'!A$1:Z$1,  0))

Reps Adjustment Sheet, Cell G4:

=IF(E4="NONE",0,COUNTIF(I:I,E4))

was replaced with:

=IF(E4 = "NONE",  0,  COUNTIF(I$1:I$500,  E4))

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi. Thanks for the advice! I tried making the changes you made, but I still got slowdown problems. I think it might have something to do wit using an xlookup twice in the IFS formula (the last column) for both the condition and for the value if true. Gonna try changing it to index and match and see if that fixes it (but basically I have narrowed it down to the last column being the issue).

[Fugdkn]

Hi there,

Take a look at the attached version of your workbook and see if it gets you moving in the right direction.

A few points:

I'm operating with Excel 2013, and therefore don't have access to the XLOOKUP function, so I've used the old-fashioned INDEX and MATCH approach;

you are applying the XLOOKUP and COUNTIF functions to entire rows (16,384 cells) and columns (1,048,576 cells) - i.e. HUGE numbers of cells, so you can certainly expect performance to take a fairly sizeable hit in such cases;

I've applied my formulas to 26 columns (A:Z) and 500 rows (1:500) for demonstration purposes. You will probably need to increase the number or columns to suit your eventual requirements, but the number of rows might be sufficient;

the array formulas which you use in column M appear slightly complicated, so I did not bother trying to convert them, and just copied the cell VALUES from your original workbook. Obviously you will need to replace these values with appropriate formulas.

Typical formula conversions are as follows:

Reps Adjustment Sheet, Cell C4:

=_xlfn.XLOOKUP(C$2,'Reps (Per Week)'!$1:$1,'Reps (Per Week)'!8:8)

was replaced with:

=INDEX('Reps (Per Week)'!A8:Z8,  MATCH(C$2,  'Reps (Per Week)'!A$1:Z$1,  0))

Reps Adjustment Sheet, Cell G4:

=IF(E4="NONE",0,COUNTIF(I:I,E4))

was replaced with:

=IF(E4 = "NONE",  0,  COUNTIF(I$1:I$500,  E4))

Hope this helps - please let me know how you get on.

Regards,

Greg M

Yup, just figured it out. It was that last column. For some reason, having an XLOOKUP for the condition in an IFS statement (and for the value if true) was slowing things down like crazy (made a separate column to break things up and it's fine now). It must be something to do with how the IFS formula works.

Thank you for your help though!

[Fluff13]

Xlookup will be faster than Index/Match.
I would also advise against using an IFS function with lookups inside. The IFS will evaluate everything inside it (ie both xlookup functions) regardless of which criteria returns true.
Use nested IF functions instead, that way only one Xlookup will be performed.

[Fugdkn]

Xlookup will be faster than Index/Match.
I would also advise against using an IFS function with lookups inside. The IFS will evaluate everything inside it (ie both xlookup functions) regardless of which criteria returns true.
Use nested IF functions instead, that way only one Xlookup will be performed.

Ah, I see. Well that's useful to know. I will take note of that in the future when considering using an IF statement (I tried to use IFS for better readability, but I guess it backfired here, and it potentially is making a lot of my formulas slower). Thanks a lot!

[Fluff13]

It must be something to do with how the IFS formula works.

See post#4

[Greg M]

Hi again,

Many thanks for your feedback.

Glad I was able to help, if only slightly.

Regards,

Greg M