Circular Reference When Using a Defined Formula

**Viral** · 07-03-2010, 11:41 AM

Hello,
I only recently started using excel and am self-taught, so I've had little guidance up to this point.
I'm having trouble with a circular reference in a set of formulas I've implemented.

What I Want to Accomplish:
Classify a percentage listed in another cell six columns over as a specific category (number).
I need to do this with a couple of cells throughout the sheet.

What I Have So Far:
In Cell
=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)="", "", MinMut)

MinMut Formula
=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=20, 1,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=30, 2,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=40, 3,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)<=50,4,MinMut2))))

MinMut2 Formula
=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=60, 5,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=70, 6,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)<=80,7,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-6)<=85,8,MinMut3))))

MinMut3 Formula
=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=89,9,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=99,10,
IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)<=100,11,"BOUNDS ERROR")))

This has given me a circular reference which I'm unable to trace through.

What I Want:
I would like to know why/where this is a circular reference.
I would like a formula solution to this problem - no macros.

I'd really appreciate help with this, I have no idea what I'm doing.
Thank you! (in advance

)

**davesexcel** · 07-03-2010, 11:47 AM

When excel informs you of the circular reference, where does it say it is?

**Viral** · 07-03-2010, 11:50 AM

It says it's in the cell I put the first IF statement in
[ =IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)="", "", MinMut) ]

I45 of the sheet I'm currently working on, and when I try to trace through cells using the circular reference toolbar it only shows I45.

**davesexcel** · 07-03-2010, 12:00 PM

What happens when you move that formula somewhere else?
It's hard to say what the problem is...I can't see the workbook.

**Viral** · 07-03-2010, 12:08 PM

There are six cells where I need this done when I put [=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)="", "", MinMut)] in any of the five other cells (say I50) nothing happens until I enter a percentage in the percentage cell (C50) at which point it designates that cell as a circular reference.

I just noticed that the circular reference dot switches between the cells in the 'I' column depending on which percentage column I updated last. When no percentages are entered the circular reference dot disappears.

**Viral** · 07-03-2010, 12:17 PM

Would this help at all?
It's a cutout of the sheet I'm working on:
\1
To the lef is the percentage column, the rightmost cell with the blue dot in it is where excel says the circular reference is.

**davesexcel** · 07-03-2010, 12:25 PM

Um...no it does not help much.....
Obviously, one of the formulas is relying on a formula inside itself.
Check out..
Here are some ways to get your question answered quickly:
In the forum rules

**Viral** · 07-03-2010, 12:50 PM

"If you want help with Excel, post an Excel file... Nobody's anxious to type data from a picture into a spreadsheet as a prelude to helping."

I'm sorry!

That does make sense and I posted quite a bit of text.

Um, the workbook has some stuff I wouldn't want floating around on the internet, so I'm attaching just the part I'm having trouble with.

This part should not rely on anything other than the percentage input in the C column.

**davesexcel** · 07-03-2010, 01:30 PM

Isn't this a circular reference question?
There is no circular reference in your example, or anything else for that matter.

**Viral** · 07-03-2010, 02:04 PM

I figured out what the problem was.

I'm using excel 2004 for mac and, apparently, it 'didn't like' having to go through OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6) for every IF statement.

I've now changed it so that is a defined formula and everything works out alright.
For anyone who comes across this problem in the future my formulas now look like this:

In Cell
=IF(offset="", "", MinMut)

offset Formula
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -6)

MinMut Formula
=IF(offset<=20, 1,
IF(offset<=30, 2,
IF(offset<=40, 3,
IF(offset<=50,4,MinMut2))))

MinMut2 Formula
=IF(offset<=60, 5,
IF(offset<=70, 6,
IF(offset<=80,7,
IF(offset<=85,8,MinMut3))))

MinMut3 Formula
=IF(offset<=89,9,
IF(offset<=99,10,
IF(offset<=100,11,"BOUNDS ERROR")))

I'll be compressing them further now that they don't take up as much character-space.

I'm sorry the excel sheet I posted didn't have anything in it. Apparently that's an incompatibility between Mac and Windows excel and sometimes comes up.

The formulas in the first post should work for Windows users. I downloaded and opened the attached file in OpenOffice and it worked fine, whereas in Excel for Mac it opened up and said there was a circular reference error.

Thanks for the help though!