Getting circular reference when using If statement precending a workday formula

[stressler]

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.

 

[Joe4]

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

 

[stressler]

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.

 

[Joe4]

Yes, the formula in Q29 is =SUM(D29:P29).
The formula in Q30 is =SUM(D30:P30).

Perhaps you have formulas in cells D29:P30 that are causing the Circular References?

Note that you can find out exactly where those Circular References reside.
Follow the instructions here: Locating Circular References | Working with Formulas and Functions in Excel 2013 | InformIT

 

[stressler]

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

=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:

=B1+C1

and then cell C1 contains the formula:

=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?
That should help you identify exactly where on your worksheet this circular reference is happpening.