|
Oct 2022
9:10am, 12 Oct 2022
6,594 posts
|
um
SPR - what sort of volume of data is it? Number of package list rows and number of years?
What do you call slow? 5 secs? 30 Sec? more than a minute?
(as a primarily SW person who worked for a hw company, I was appalled by their ethos of 'throw more hardware at it' rather than improve the SW logic ...  )
|
|
Oct 2022
9:35am, 12 Oct 2022
38,497 posts
|
SPR
25 secs for every 2% as I'm currently calculating after a formula update on the original file so that's just under 21 min. The original file had 40,000 rows of packages and over two years of data. I've trimmed it to 12 months as thinking bringing relevant values (those over a threshold cost is what I'm interested in but you need to do the calc first) across from previous years rather than the whole thing would make more sense even if you bring previous years across as values. The 12 month file is much more reasonable and calculates for longer than a minute but less than I think (though big changes like the cohort take a while initially).
|
|
Oct 2022
9:35am, 12 Oct 2022
38,498 posts
|
SPR
3M I'd love the hardware solution, lol.
|
|
Oct 2022
9:36am, 12 Oct 2022
38,499 posts
|
SPR
but less than 5 was supposed to be the last sentence in 38,497
|
|
Oct 2022
9:42am, 12 Oct 2022
6,597 posts
|
um
My initial quick though is to add the month(s) to the package list tab with a one off calculation column, then reset the formula to use that rather than the multiple index functions for each line? Or even a column for each month with a flag to show if the package was current in that month.
Even if something like a string of the months the package covered and a find, although there's probably a more elegant way to store that.
|
|
Oct 2022
10:10am, 12 Oct 2022
19,325 posts
|
3M (aka MarkyMarkMark)
It does sound like an "tidying/ordering" of data and identifying the logic steps before processing might help a bit. (But I still like plugging in new hardware too!)
|
|
Oct 2022
10:36am, 12 Oct 2022
1,237 posts
|
Sam Jelfs
Sounds to me, from first glance, that you need a proper database and not excel
|
|
Oct 2022
11:05am, 12 Oct 2022
38,500 posts
|
SPR
That wouldn't work. You'd need 365 days as it's not about the month in general, it's each day in the month
So a client could have 2 packages from 1-10 Oct, 3 from 11-15, 4 from 16-25, then 3 again 25-31. I am checking the cost on each day and returning the max in the month.
Might be better in Access but my Excel skills are better than Access, lol.
|
|
Oct 2022
11:11am, 12 Oct 2022
6,599 posts
|
um
Maybe I'm not understanding SPR. But all of those examples I'd flag in the package sheet as Oct, and include in the max calc for Oct. I woiuldn't go off checking for the Oct against the calendar sheet more than once.
|
|
Oct 2022
11:33am, 12 Oct 2022
38,501 posts
|
SPR
You can't flag as Oct as you'd over estimate the max cost.
Read the different scenarios as separate packages open in Oct with different costs.
|
