Circular arguments

Question

Circular arguments

Kevin OHara 0

How can I find a circular argument within an Excel preadsheet? I am using IOS.

0 comments

2 answers

Your answer

Answer 1

A circular reference happens when a formula refers to itself directly or indirectly.

On iOS, Excel has limited formula auditing tools, so the practical way to find a circular reference is to open the workbook in Excel for Windows or Mac desktop.

Steps:

Open the workbook in the desktop Excel app.
Go to Formulas > Error Checking > Circular References.
Select each listed cell address to jump to the problem cell.
Fix the formula so it no longer points back to itself.
If the issue spans multiple cells, use Formulas > Trace Precedents or Trace Dependents.
Repeat until the status bar no longer shows Circular References.

If Circular References is unavailable:

Select a worksheet cell first.
Try Formulas > Error Checking again.
Use the Excel search box and search for Error Checking or Circular References.

If the warning continues after editing, another circular reference may still exist on a different worksheet or in another open workbook.

Answer 2

On Excel for iOS, the full circular-reference auditing tools available in the desktop version are not provided. If Excel detects a circular reference, it may display a warning, but finding an indirect circular reference can be difficult on the iPhone or iPad.

A direct circular reference is straightforward. For example, if cell A1 contains:

=A1+B1

then A1 refers to itself.

An indirect circular reference is more difficult because the loop can involve several cells:

A1 → B1 → C1 → D1 → A1

For example:

A1: =B1+C1
B1: =D1*2
D1: =A1/2

Here, A1 → B1 → D1 → A1 creates the circular reference.

If you are restricted to iOS, you might want to start with cells whose formulas were recently changed or that produce unexpected results, examine the formula in the formula bar, and follow each referenced cell. Keep following the references until you either reach a cell with no dependency on the original cell or return to the starting cell. If the workbook is large, opening it in Excel on Windows or Mac is much easier because the desktop version can identify circular references and trace the precedent/dependent chain.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin