Would using FORMULAS in NAMES reduce Processing Time or File Size ??

[Sunny54321]

I have a large table of data that spans 24 columns and 18K of rows. I want to have an output that scans the mass data and returns 24 columns of only 365 rows (1 year or maybe 2). That will further be processed into a few rows calculating Std Dev, Median, Mean, Percentile, etc.

In terms of efficiency, is it better to have every cell of the 24x365 use a semi complicated formula or is it more efficient to put the "core" of the formula in a "NAMES" and use that Names reference into each cell (thereby having a smaller text length to the formula)?

I want to increase processing speed and reduce file size.

Any input is welcome.

Thanks,
Sunny

 

[shg]

Using names ranges will certainly reduce file size by making the formulas more compact. Avoid the use of volatile functions (specifically, INDIRECT and OFFSET) so that the range names are not volatile.

 

[Sunny54321]

The formula I have contains a "SUMIFS" with 5 criteria embedded in a "SUMPRODUCT" equation.

There are 24 x 800 cells of this formula on a "calculation helper" table.

If I use Names ranges using a formula with the 5 criteria in the SUMIFS, will this slow down processor speed?

If so, more important is to have fast processor speed (for now).

Thanks,
Sunny

 

[Aladin Akyurek]

The formula I have contains a "SUMIFS" with 5 criteria embedded in a "SUMPRODUCT" equation.

There are 24 x 800 cells of this formula on a "calculation helper" table.

If I use Names ranges using a formula with the 5 criteria in the SUMIFS, will this slow down processor speed?

If so, more important is to have fast processor speed (for now).

Thanks,
Sunny

What is the formula you are trying to rewrite using named ranges?

 

[Sunny54321]

Hi,


I have a few formulas and want to try and find any area where I can improve process time and reduce file size.
This formula is per each cell of the 24x800 grid:
=SUMPRODUCT(SUMIFS(AC$13:AC$16655,$B$13:$B$16655,$BH32,$BA$13:$BA$16655,$BG$14,$BB$13:$BB$16655,$BH$20:$BH$23))

This formula is a Work Day identifier to convert dates into T-F or Sat or SUN/HOLIDAYS so they can be separated out. If there is a way to reduce this, it would help as well:
=IF(WEEKDAY(BH32)=1,8,(IF(WEEKDAY(BH32)=7,7,(IF(NETWORKDAYS(BH32,BH32,HOLIDAY_SCHED)=0,8,6)))))

There may be a couple of extra formulas as well but these two come to mind.
Thanks for your help. I can put the file on DropBox Public if you were interested in looking a little closer.

Thanks!
Sunny

 

[Aladin Akyurek]

Is $BB$13:$BB$16655 the only range for which you need to run multiple criteria at a time?

 

[Sunny54321]

Is $BB$13:$BB$16655 the only range for which you need to run multiple criteria at a time?

Hi,

about 1.4 years of "raw" data gets down to 16655. I wanted to make a system to process over 2 years.

The way it is setup now (and it can be changed), is that I paste "raw" data (is about 30 columns wide and goes down to row 16655 in this example), 24 columns of that data is then "processed" to remove double events in a second table. 2 more columns also process raw data - 1 column to categorize multiple Locations into 4 Zones (use 4 text comparisons .... look for first two letters to compare) and another to adjust 5 items into 4 types.

The equation above is from ONE OF THREE "CALCULATION HELPER TABLES" that look at the "processed" data table and use criteria to narrow down according to the user selecting parameters of date, "Zone" or "Type". Also, dates are tagged as M-F or SAT or SUN/HOLIDAY so the user can pre-select those sub-time windows over the period searched.

I was going to use a separate file to paste raw data, let it process that and then use that "processed" data and copy it into the CALCULATION & RESULTS file for processing. That would remove one 30x16655 set of small comparison formulas.

But at this point, I am wondering as to how can I reduce the "CALCULATION HELPER TABLES" of which the formula above is used .... 24 cells wide that takes the 16655 rows and pulls it down to 400 (about 400 days in the 1.4 years)...

Any suggestions are welcome.

 

[Aladin Akyurek]

Hard to judge whether your set up is the reason for the unsatisfactory speed you experience.

My question was motivated whether trading off memory against speed could be an option here. Such can be effected by concatenating some of the ranges into one if the original ranges are mainly tested for equality in formulas that are required.