# Getting circular reference when using If statement precending a workday formula

#### stressler

##### Board Regular
While trying to create a formula that says if a certain field is blank, return blank, otherwise return the date for the workday formula. Here's the formula I'm using... I'll list two as I am pulling the workday less 1 business day, then less two business days, then less 3 business days, etc... all the way out to less 60 business days.

Formula to show today's date if field Q29 doesn't equal 0: =IF(Q29=0,"",WORKDAY(TODAY(),0,\$S\$2:\$S\$13)) The field I'm using this formula in is C31.
Formula to show last business day's date if field Q30 doesn't equal 0: =IF(Q30=0,"",WORKDAY(TODAY(),-1,\$S\$2:\$S\$13)) The field I'm using this formula in is C32.

I keep getting circular reference error when I try to execute this formula with the If statement included. Attaching my circular reference error, I am unable to figure out what the circular reference is.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Joe4

Do you have any formulas in cells Q29:Q30 or S2:S13?
If so, what exactly are they?

#### stressler

##### Board Regular
Yes, the formula in Q29 is =SUM(D29:P29).
The formula in Q30 is =SUM(D30:P30).
There are no formulas in S2:S13, that is my list of holidays.

#### stressler

##### Board Regular
Okay. Perhaps I should tackle this differently, I am getting the circular reference error. I know it's because I'm using fields with formulas in them as part of my formula. I need to know how to get around this.
Since field Q29 has a formula in it to sum fields D29-P29, then I'm using the sum of field Q29 to calculate the results of my formula in field C20, perhaps I could tackle this differently. How can I create a workday formula that only calculates the date if the sum of fields D29:P29 doesn't equal zero. If the sum of fields D29-P29 is anything other than zero, then I want to calculate the date. Here's what I have, but it's not working...
=IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,\$S\$2:\$S\$13))
Where D29 through P29 have formulas in them to calculate their totals.
The formula I have in D29 is... =IF(\$C29="","",COUNTIFS('ALM Export'!\$H:\$H,">"&\$C30,Summary!I:I,D\$2))

So my real question is this... I have come up with this formula =IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,\$S\$2:\$S\$13)), but I don't think it's working because D:29-P29 are fields that their totals are calculated by a formula as well. So how can I fix this formula so that it allows me to calculate the sum of fields D:29-P:29, even though D:29-P29 are using formula to display a total. Then if the sum of D:29-P29 is 0, display blank, if the sum of D:29-P29 totals more than zero, then do the workday formula?

#### Joe4

So my real question is this... I have come up with this formula =IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,\$S\$2:\$S\$13)), but I don't think it's working because D:29-P29 are fields that their totals are calculated by a formula as well
No, that is not the problem. Excel has no problem with formulas being based on other formulas.

Do you understand what a "circular reference" is? It is when a formula either directly, or indirectly calls itself. You cannot do that without using "iteration" or VBA.

An example of a direct circular reference would be something like this:
In cell A1, you have the formula
Rich (BB code):
``=SUM(A1:C1)``
So the formula in the cell actually contains a reference to the cell that the formula is contained in (A1)! Excel is not sure what to do with that.

An example of an indirect circular reference might look like this:
In cell A1 is the formula:
Rich (BB code):
``=B1+C1``
and then cell C1 contains the formula:
Rich (BB code):
``=A1+10``
So the formula in cell A1 is indirectly calling itself (by way of cell C1).

What Excel is telling you is that you have one of these situations somewhere on your worksheet. Most likely, it is an indirect circular reference (or else it would probably be obvious to you).

Did you try following those steps I posted in my previous post?

Replies
5
Views
169
Replies
3
Views
151
Replies
2
Views
238
Replies
5
Views
301
Replies
1
Views
96

1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back