I have a QP9 spreadsheet which carries out most of its processing via a
macro, including calling {HISTOGRAM} a few times to generate
intermediate results.

I believed there was no point in letting the spreadsheet do any
recalculations until the {HISTOGRAM} calls had been executed, so I set
the recalculation options to 'Manual'.

However I have subsequently found that including a {CALC} at the point
where the spreadsheet has all the right numbers in all the right places
to come up with the final answer doesn't work. {CALC}{CALC} is also
insufficient. By experiment, it needs {CALC}{CALC}{CALC}.

I left the other Recalc settings to default - Order = Natural and # of
iterations = 1.

Am I right to set Mode = Manual so that QP9 doesn't do any calculations
until the data is in place?

Why isn't a single {CALC} sufficient to generate the final answers, and
is there a way I can force QP9 to recalculate thoroughly, such that I
don't need to experiment with the repeat calls to {CALC}?

Thanks for your help,

NigelH

NigelH wrote:
>
> I have a QP9 spreadsheet which carries out most of its processing via a
> macro, including calling {HISTOGRAM} a few times to generate
> intermediate results.
>
> I believed there was no point in letting the spreadsheet do any
> recalculations until the {HISTOGRAM} calls had been executed, so I set
> the recalculation options to 'Manual'.
>
> However I have subsequently found that including a {CALC} at the point
> where the spreadsheet has all the right numbers in all the right places
> to come up with the final answer doesn't work. {CALC}{CALC} is also
> insufficient. By experiment, it needs {CALC}{CALC}{CALC}.

Strange indeed. The need for repeated {CALC}s could be due to circular
references, but you would likely be aware of that circumstance without any
outside pointers. So, a couple of issues/questions come to mind:

1. Does the file have external links?

2. Is "manual recalc" the file's default setting, or is that its status only
during macro execution?

3. If you perform the macro's steps manually (with recalc mode set to
"Manual"), do the "right numbers appear in all the right places" if you press F9
only once and wait?

> Am I right to set Mode = Manual so that QP9 doesn't do any calculations
> until the data is in place?

I use this technique of suspending automatic recalculation in some of my (QP8)
macros, but have never encountered your type of problem -- one {CALC} is always
sufficient to bring everything up-to-date. However, I only suspend
recalculation during macro execution, i.e., my spreadsheets always "spin their
wheels" as soon as they are opened.

> Why isn't a single {CALC} sufficient to generate the final answers, and
> is there a way I can force QP9 to recalculate thoroughly, such that I
> don't need to experiment with the repeat calls to {CALC}?

You could try two different things, both of which hold promise only if your
problem is macro-specific, not file-specific:

1. {CALC}{WAIT ...}

2. {CALC}{RECALC location, condition}

With respect to option 2), you would have to distill the requirement to have
"all the right numbers in all the right places" into a suitable formula
somewhere in your spreadsheet. {RECALC} could then be made to test for that
condition being met before allowing the macro to execute {HISTOGRAM}.

Cheers,
Uli