• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Having a cell keep its value under certain conditions to avoid circular logic

O

OleMiss2010

Member
I have a quarterly calculation that is updated each month. In cell B28 I have to number from last quarter. In cell B29 I have the target for this quarter (as of the current month-end). In cells B31:B33 I have the difference in the month-end target and the previous quarter (for example, B31 has the difference in Jan. 31 target and Dec. 31 calculation). I would like for the months to automatically update. However, because B29 is updated each month (essentially it is a running total until the quarter ends when it becomes the value for b28) I run into a circular reference when trying to apply a formula to B31:B33.


Month 3 (March, June, September, and December) is easy. With A2 holding the current EOM date the formula is:

=IF(OR(MONTH(A2)=3,MONTH(A2)=6,MONTH(A2)=9,MONTH(A2)=12),B29-B28-B32-B31,0)

This gives me the value of whatever needs to be added to the calculation for that quarter after the previous two months have been accounted for. The trick is having month 1 essentially hold its value if it's not January, April, July, or October.


Is there some formula variation that I'm just not thinking of, or is this just going to have to be a manual input every month, which truthfully isn't that much trouble?
 
Hi OleMiss2010, I can't visualize and connect all the talking points. Could you please upload a file so I can see what you are asking (the driving cells and dependent cells)? Thanks.
 
Hi, OleMiss2010!

I believe that an uploaded file will help too. In the meantime, if you're using that formula you can try changing it to this:

=SI(RESIDUO(MES(A2);3)=0;B29-B28-B32-B31;0) -----> in english: =IF(MOD(MES(A8),3)=0,B29-B28-B32-B31,0)

Regards!
 
Unfortunately I can't upload a worksheet, but I will provide a sample of similar information below, just run this macro in a worksheet and it will auto-create the worksheet. Column C was added as a description column.

[pre]
Code: 
Sub Macro1()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Previous Quarter"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Required this Quarter"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Month 1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Month 2"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Month 3"
Range("B1").Select
ActiveCell.FormulaR1C1 = "11/30/2011"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1000000"
Range("B3").Select
ActiveCell.FormulaR1C1 = "1500000"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Manually entered"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Manually entered during month 1 of each quarter. Represents value from B3 at the end of the previous quarter's month 3"
Range("C3").Select
ActiveCell.FormulaR1C1 = "Calculation from other information. This is the total at the end of each month. In this example month 1 and 2 should sum to $500,000 since it is the end of Nov. and $500,000 more is needed."
Range("C4").Select
ActiveCell.FormulaR1C1 = "This cell should be B3-B2 when the month is Jan, April, July, or October; otherwise it should retain its value."
Range("C5").Select
ActiveCell.FormulaR1C1 = "This cell should be blank in Jan, April, July, or October; it should be b3-b2-b4 in Feb, May, Aug, or Nov; it should retain its value in March, June, Sept, or Dec"
Range("C6").Select
ActiveCell.FormulaR1C1 = "This cell should be blank in Jan, Feb, April, May, July, Aug, Oct, or Nov; it should be b3-b2-b5-b4 in March, June, Sept, or Dec"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
[/pre]

Hopefully this is clear.
 
Hi, OleMiss2010!

With Excel formulas I don't get how you could do that, but with a little VBA code in the Worksheet_Change event, you can do it easily.

Give a look at this and try it.

http://www.2shared.com/file/6ApRzmdg/Having_a_cell_keep_its_value_u.html

Regards!


EDIT: 22/08/2012


Hi!

New download link since 2shared became erratically available:

https://dl.dropbox.com/u/60558749/Having%20a%20cell%20keep%20its%20value%20under%20certain%20conditions%20to%20avoid%20circular%20logic%20%28for%20OleMiss2010%20at%20chandoo.org%29.xlsm

Regards!
 
I was hoping for a formula only option, but suspected VBA was the only way to do it. I couldn't think of a logical way to give the commands it needed.
 
Hi!

New download link since 2shared became erratically available:

https://dl.dropbox.com/u/60558749/Having%20a%20cell%20keep%20its%20value%20under%20certain%20conditions%20to%20avoid%20circular%20logic%20%28for%20OleMiss2010%20at%20chandoo.org%29.xlsm

Regards!
 
@SirJB7

Why not just edit your previous posts rather than reviving all these old threads? Or you just looking for some free post count padding? =)
 
@Luke M

Hi!

You're right, I didn't realize that, I'm gonna fix it now.

Thanks for the catch.

Regards!

PS: Being asked about, tried to access those links and didn't work...

PS2: After editing I'll clean it up.
 
You must log in or register to reply here.
Back
Top