• 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.

Need help stopping output when result might be negative, without circular references.

AndrewS

New Member
Hello,

I'm having trouble with an issue. I have some columns projecting savings contributions over time (from column N to AA), and my problem is with the "TFSA" section, namely the "Unused contribution room" in column Q. (For context, TFSAs are a Canadian savings account type that have limited contribution room, so I want to be able to add to it without exceeding that limit.) The limit is off to the right somewhere.

This column (Q) is fed by direct contributions to this section, as well as outgoing moneys from the other sections (non-registered savings, and join non-registered savings).

I could easily make an IF statement in Q (the Unused Contribution Room ) telling it not to decrease itself if it would be below zero, however that would not balance well with the rest of the sheet, because the other sections that feed it (the non registered savings areas) would still display transfers going out.

I need a way for the columns that feed Q (non registered savings) to stop doing so when it would make Q drop below zero, but without directly referencing Q, because that would create a circular reference.

All columns that transfer into or feed the Q column are first aggregated into column R. Those columns are T and U, and U is an aggregate of X, AA and AC).

In the sample file (just cut out a snippet of my program), you'll notice I've referenced the cell above the corresponding cell in Q in order to avoid circular references, however this creates ugly looking jumps and gaps, and not what I want. The columns feeding Q should STOP outputting / stop populating with negative values when Q becomes negative.

Edit: As in the legend, the blue areas are input and the reddish are output. The blue boxes in row 5 are what populate most of the columns here. You can type Y to contribute the max, or a custom number.

Any help is greatly appreciated!
 

Attachments

  • AndrewS Sample.xlsx
    93.5 KB · Views: 2
Last edited:
I think you could achieve your goal by using Data Validation. In your example, select cell S8. Apply a custom validation of:
=S8<=$Q8

Copy this validation to other cells that feed into col Q. As long as you don't try to change multiple cells at the same time, I think this will work.
 
Thanks for the suggestion.

Wouldn't that create a circular reference?

Edit: going to try and work with it, give me a few minutes
 
You'd think so in a normal calculation, but Data Validation appears to only get checked as you're entering data. So, you type a number, hit Enter, XL checks the validation, and then recalculates the formulas. Since Data Validation isn't recalculated all the time, and only at entry, it follows a sequence, rather than creating a circular loop.
 
Ok I have some questions :)

1- Did you really mean to use S8 for your suggestion? I don't understand why I'd need the code there as that column is for something else. If you did mean to have it there (or just outside the columns in question), let me know and I'll make a new soon-to-be-hidden column for it.

2- When you say to apply a custom validation, is that some kind of function or excel operation or just code?
 
1. I just picked col S as I thought it fed into col Q. You can make it whatever you want, just need to make sure that the active cell is the same one you reference in formula.

2. To apply a formula for Data Validation, you need to pick Custom from the dropdown.
upload_2015-3-6_12-27-54.png
 
Can you elaborate on what "didn't work" means? Did the formula create a circular error, unexpected results, no results, XL crashed, blue smoke escaped from your machine, ...?
 
Sorry! It produced no results.

I'm not incredibly experienced with the data validation section, but perhaps you could give it a shot and see if you could get it to work?

In essence I need to do a circular reference without causing a circular reference error =\
 
Hi Andrew ,

The problem is unfixable provided the description is unclear.

If you can define a problem clearly , half the job is done.

A clear definition is one which states :

Only the essentials - this means try to exclude everything that is irrelevant ; an explanation of TFSA does nothing to increase someone else's understanding of the problem in your Excel worksheet , however useful it may be in another context.

Covers all aspects of the problem

Does so in the briefest number of words.

Let me try and understand what you have already explained :

1. Your worksheet column Q has a formula which is dependent on columns S , T and U if we ignore the lookup portion.

2. Column S can be ignored since it is blank.

3. Column T is a fixed value , which can be either 0 or +2000.

4. Column U is dependent on columns X and AA , which again are fixed values of 0 or negative values.

The following sounds very well explained :

The columns feeding Q should STOP outputting / stop populating with negative values when Q becomes negative.

but is it not open to interpretation ? What does it actually mean ? Does it mean the values in columns T and U should become 0 when Q goes negative , or does it mean that the formula in column Q should stop using them if it is going to go negative ?

Making it clear involves using specific Excel worksheet cell references ; in your worksheet , the first negative value is in cell Q19. What do you want should happen in this cell ? What should happen in the precedent cells ?

Take the time to explain this example in detail , and you will get your solution.

Narayan
 
Thanks for your response. All of your 'assumptions' about my worksheet are correct; that is exactly how it functions.

Recap of salient details:
- Q is the ultimate destination
- S, T and U feed directly into Q
- X and AA feed into U
- the above is a tiered aggregator (feeds from one tier to the next, ultimately all into Q).

AND
- The understanding that the sheet is an overview of somebody's savings accounts: It's designed so that the other accounts (not Q) can be funneled toward the Q account over time, however Q has limited free space (the lookup value minus everything already in Q). So when the free space (Q) is 0 or less, the other accounts need to stop trying to add money to it. Because there is nowhere for that money to go.

In response to your questions about my desired outcome:

The columns feeding Q should STOP outputting / stop populating with negative values when Q becomes negative.

but is it not open to interpretation ? What does it actually mean ? Does it mean the values in columns T and U should become 0 when Q goes negative , or does it mean that the formula in column Q should stop using them if it is going to go negative ?

I mean that as soon as Q becomes negative, the corresponding row in columns X and AA, and all future column entries in X and AA after this row, should be 0.

Example: In the sample, Q19 < 0. When this happens, X19 should = 0 and AA19 = 0, as well as the remainder of the X column and the AA column after cell 19.
 
Back
Top