Team ZR-1 said:
Max thanks again for the help
You're welcome !
2 questions, being in real world there can
be up to 30,000 rows when I added
formula just for the 1st cell ( had 15,000 rows of data)
the laptop which is a 1 Ghz CPU Sony with
128 meg RAM free with MS 2000 OS as soon as I hit sht
ctl enter the excel was in calculate and CPU
went to 100 % in use and was like that for 15 minutes !
leaving no CPU for the laptop.
Is there some option setting in Excel
to prevent this from taking so long to calculate one cell ?
For calculation intensive applications,
I'd set the calc mode to Manual
Click Tools > Options > Calculation tab
Check: Manual > OK
This setting will hold "unnecess" calc until calc is required
When calc/recalc is required, press F9
Another thing we could do is to simplify the last formula.
For eg: remove the error-trap, and just make do
with the "plain" array formula in A6:
=AVERAGE(IF((MapKpa>=$A4)*(MapKpa<$B4)*(RPM>=C$2)*(RPM<C$3),grmcyc))
This should speed things up a bit,
but the "ugly" error returns would be visible
To hide these w/o using the error-trap in the formula,
we could conditionally format the all formula cells
to mask those with error returns by
choosing a font color to match the fill color
For eg: in the sample file's sheet VE,
the array formulas are in C4:I21
Select C4:I21
Then click Format > Cond Formatting
Formula is: =ISERROR(C4)
Click Format button > Font tab > choose light blue* font color > OK out (as
the fill color is light blue)
Another thing we could do is dispense with the use of the 3 dynamic ranges
in the earlier set-up.
(These dynamic ranges use OFFSET which is volatile)
Use 3 fixed* defined ranges instead, eg:
(*but not entire col refs in this instance, as explained earlier)
grmcyc: =Data!$M$2:$M$1000 (say)
MapKpa: =Data!$N$2:$N$1000
RPM: =Data!$C$2:$C$1000
Note that the 3 defined ranges should be identically structured, and use the
smallest range possible. The "1000" illustrated above may be excessive.
You could tinker with applying the above alternatives in the earlier sample
provided, get these working ok first, then apply them to your actual file.
The calc performance should improve, but by what extent, I don't know.
Also what easy way is there to copy the
1st cell's formula to properly copy
to all other cells in the table and
properly change the variables ?
Not sure what you mean by "properly copy". I'd usually fill formulas by
dragging the fill handle (i.e. the black "box" at the bottom right-corner of
the anchor cell - eg: C4) across / down after entering the formula there.
Besides presentation value ("easy-to-read"), designing the table layout is
also important, especially where the anchor cell's formula needs to change
relatively and point to the correct values in both the top row(s) / left
col(s) as we copy it across and down to populate (Thought the table design
proposed earlier in the sample was quite decent, no? <g>)
Using a dollar sign in the cell ref ($) will fix the col / row ref. Eg: $A4
means we're fixing the point to col A: "A" as we copy across, but we want
the row ref ("4") to change when we copy down. Conversely for A$4. And if
we fix both col / row ref ($A$4), it means we don't want the formula's point
to this cell: A4 to change whether we're copying across or down. Conversely
for a cell w/o any dollar signs: A4.
---