Msg - Function in formula causes result to change when excel calculates

[Cammandk]

I thought this formula was ok but the result keeps changing. When excel calculates its ok and then I go and do something and suddenly its not.
When I went to evaluate formula if gives the text above.
Any ideas?
Thanks DK.


=IF(ISBLANK(!$AB34),IF(!$I34="R",0,IF(TEXT(EDATE(IF(!$AR34="P",TODAY(),!$J34),IF(!$AR34="P",0,!$AR34)/30),"yyyymm")=TEXT(!AV$5,"yyyymm"),!$T34,0)),IF(!$I34="R",0,IF(TEXT(EDATE(IF(!$AR34="P",TODAY(),!$AB34),IF(!$AR34="P",0,!$AR34)/30),"yyyymm")=TEXT(!AV$5,"yyyymm"),!$T34,0)))

AB34 is either blank or a date
I34 is either blank or R
AR34 is either blank or "P"
J34 is either blank or date
AV5 is date
T34 is blank or number

 

[Luke M]

The message is because this is a volatile formula (meaning it gets recalculated every time) due to the TODAY function.
What exactly is the overall goal of this formula?
I noticed first that the only different between first IF's two outputs is whether to use AB34 or J34. We can shorten this formula greatly by moving that decision like so:
=IF($I34="R",0,IF(TEXT(EDATE(IF($AR34="P",TODAY(),IF(ISBLANK($AB34),$J34,$AB34)),IF($AR34="P",0,$AR34)/30),"yyyymm")=TEXT(AV$5,"yyyymm"),$T34,0))

Next, don't need to check AR34="P" twice. We can then shorten formula to:
=IF($I34="R",0,IF(TEXT(IF($AR34="P",TODAY(),EDATE(IF(ISBLANK($AB34),$J34,$AB34),$AR34/30)),"yyyymm")=TEXT(AV$5,"yyyymm"),$T34,0))

The only two possible outputs are T34 or 0, so we'll use an OR function so that both are outputs are at end of formula, making it easier to read.
=IF(OR($I34="R",TEXT(IF($AR34="P",TODAY(),EDATE(IF(ISBLANK($AB34),$J34,$AB34),$AR34/30)),"yyyymm")<>TEXT(AV$5,"yyyymm")),0,$T34)

Now, I can read the formula easier. From this, we can see what can cause the result to change. You said that AR34 is either blank or "P". However, if it's blank, then we are also looking at AR34/30 to determine how many months we should move from TODAY. Is this intended?

Finally, when you say a cell is blank, is it truly blank (no contents), or is it a formula evaluating to ""?

 

[Debraj]

Hi DK..

Within a week.. three different Post.. every time confirming solved.. and then again you came back with twist..
but you have still not show us a view of your sheet.. after a lots of request..

Is it really hard to create a dummy copy of your sheet .. for us.. or we still have to give you solution to test and came with twist..

 

[Cammandk]

Hi Luke

Sorry for the delay in responding to this.

I now understand that by using TODAY this is creating a volatile formula.

I also understand what you have done with the formula. I made one error in that the AR34/30 should have been AS34/30. This is picking up a number value of 0 or 30 or 60.
I revised this and the formula is working.

However I am still getting an error in my workbook with some fields not calculating until something in the formula is revised.

I have a very large and complicated workbook that I could not just upload and was unsure of how to strip out a sheet that would then operate correctly.

In response to Debraj I have now tried to do this.

I hope you can follow the logic through of what I have submitted and that you can find the problem.
This sheet is basically 1 of 40 that are identical and therefore with the formulas not working correctly the integrity of the whole workbook is compromised at this point.

Thanking all in advance.

There are no macros in this file - I had to save as this to get defined names enabled?

DK

 

[Luke M]

In your named ranges, some of the formulas use OFFSET or INDIRECT. These are also volatile functions. Might be worth checking these out.

Also, there is a limit on cell dependencies when XL stop trying to recalculate every time. It's *possible* that with so many worksheets & named ranges, you've hit this limit.
Further reading:
http://www.decisionmodels.com/calcsecretsf.htm

 

[Cammandk]

Thanks for reply.
I'll look further into.
DK.