# 30-Oct-2008 Using INDIRECT to refer to cells on Analyse-it analysis reports

A customer contacted us last week to ask how to refer to cells on an Analyse-it report worksheet, from a formula on another worksheet. The customer often used Analyse-it's refresh feature, to repeat the statistical analysis and update the statistics, and direct references to cells on the report were being lost on refresh.

As an example, suppose you have used Analyse-it linear regression to calculate the linear relationship between installation cost and the number of employees required, distance to the site, and the cost of machine being installed. Analyse-it would calculate the effect of each variable on the final cost, technically known as regression coefficients, which you can then use to predict installation costs for jobs in future.

You might setup a worksheet to predict and quote installation costs for future jobs. You could use an Excel formula to reference the coefficients directly from the Analyse-it report, for example:

= Employees * CostAnalysis!C17 + Distance * CostAnalysis!C18 + MachineCost * CostAnalysis!C19

By directly referencing the coefficients calculated by Analyse-it in your formula, you can be sure you’re using the exact values with no chance of error.

If you’ve used this technique before, you already know the problem. When you refresh the Analyse-it report, to repeat the analysis and recalculate the statistics, references to cells on the report worksheet are broken. In the above example, the references to cells on the *CostAnalysis* worksheet become #REF!, for example:

= Employees * #REF! + Distance * #REF! + MachineCost * #REF!

#REF! simply means the reference is broken and refers to a cell that no longer exists.

The reason the cells no longer exist is because when you click Refresh, Analyse-it repeats the analysis, creates a new report worksheet to present the statistics and charts, then finally deletes the old worksheet and replaces it with the new. The cell references are broken when Analyse-it deletes the old report worksheet.

Although not perfect there is a simple workaround for the problem using the INDIRECT function. Rather than refer directly to a cell, you wrap the cell-reference in INDIRECT(“..”), to indirectly refer to it. For example, the installation cost prediction formula would become:

= Employees * INDIRECT("CostAnalysis!C17") + Distance * INDIRECT("CostAnalysis!C18") + MachineCost * INDIRECT("CostAnalysis!C19")

If you now refresh the analysis, for example because you have collected more data, or found an error in the original data, the formula will still work. Cell references won’t be broken, and will refer to the latest calculated coefficients.

Analyse-it Cuts Project Time in Half at Swiss Lab

Normal quantile & probability plots

##### Tags

##### Latest posts

- A New Year. A New Edition.
- Analyse-it v6.10: Survival Analysis and other improvements
- Analyse-it v5.90: Support for the updated CLSI EP6-Ed2 protocol and inverse predictions
- Analyse-it 5.50 to 5.65: Recent improvements
- COVID-19: Calculating the detection limit for a SARS-CoV-2 RT-PCR assay using Analyse-it
- COVID-19: Calculating PPA/NPA agreement measures using Analyse-it
- Diagnostic accuracy (sensitivity/specificity) versus agreement (PPA/NPA) statistics
- COVID-19: Establishing the diagnostic accuracy (sensitivity/specificity) of a test using Analyse-it
- Why the diagnostic test 'accuracy' statistic is useless
- Sensitivity/Specificity and The Importance of Predictive Values for a COVID-19 test

##### Most popular posts

- Announcing the Analyse-it Quality Control & Improvement Edition
- Analyse-it 4.0 released: Support for CLSI guidelines, and Measurement Systems Analysis
- Analyse-it 3.80 released: Principal Component Analysis (PCA)
- Recent improvements in Analyse-it 3.76 and our first video tutorial!
- Our software development and validation process
- The numerical accuracy of Analyse-it against the NIST StRD
- Quantiles, Percentiles: Why so many ways to calculate them?
- Handbook of Parametric & Non-parametric Statistical procedures
- A sombre note: Professor Rick Jones

## Comments

Comments are now closed.