Circular

[Margo]

Problem:

Cell A1 is data entry =quantity on hand
Cell B1 is data entry = quantity sold
Cell C1 is data entry = quantity received
Cell D1 is formula = new quantity on hand (A1-B1+C1)

What I want to do is have only one 'quantity on hand' column.

A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
formula I use, IF, AND, etc., it continues to be circular as long as the
value in A1 is part of the formula.

No doubt the answer is perfectly simple, and I'm just not asking Help the
right question. After all, every inventory program must need that basic
function.

 

[Niek Otten]

Hi Margo,

First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
Second advice: Look here:

http://www.mcgimpsey.com/excel/accumulator.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Problem:
|
| Cell A1 is data entry =quantity on hand
| Cell B1 is data entry = quantity sold
| Cell C1 is data entry = quantity received
| Cell D1 is formula = new quantity on hand (A1-B1+C1)
|
| What I want to do is have only one 'quantity on hand' column.
|
| A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| formula I use, IF, AND, etc., it continues to be circular as long as the
| value in A1 is part of the formula.
|
| No doubt the answer is perfectly simple, and I'm just not asking Help the
| right question. After all, every inventory program must need that basic
| function.
|
|

 

[Margo]

Thank you very much for the response and the link. It's all beyond my
understanding though at this time ... guess I'm going to go take a Visual
Basic course.

 

[Niek Otten]

<guess I'm going to go take a Visual Basic course>

You could, of course.

But what remains is that circular references and similar concepts should not be used, unless it is the very nature of the problem
description itself, like for some recursive calculation algorithms.
For record-keeping structures, like the one you describe, I strongly advise NOT to use circular references.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thank you very much for the response and the link. It's all beyond my
| understanding though at this time ... guess I'm going to go take a Visual
| Basic course.
|
| "Niek Otten" wrote:
|
| > Hi Margo,
| >
| > First advice: Don't do this! Any accidental recalculation will change your quantity, possibly without you even noticing!
| > Second advice: Look here:
| >
| > http://www.mcgimpsey.com/excel/accumulator.html
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Problem:
| > |
| > | Cell A1 is data entry =quantity on hand
| > | Cell B1 is data entry = quantity sold
| > | Cell C1 is data entry = quantity received
| > | Cell D1 is formula = new quantity on hand (A1-B1+C1)
| > |
| > | What I want to do is have only one 'quantity on hand' column.
| > |
| > | A1 = A1-B1+C1 which is a circular reference, and no matter what sort of
| > | formula I use, IF, AND, etc., it continues to be circular as long as the
| > | value in A1 is part of the formula.
| > |
| > | No doubt the answer is perfectly simple, and I'm just not asking Help the
| > | right question. After all, every inventory program must need that basic
| > | function.
| > |
| > |
| >
| >
| >

 

[Gord Dibben]

To add to Niek's "strong advice".

Doing it as you describe will leave no "paper trail" of inputs so very difficult
to troubleshoot any errors in data entry.

Excel has lots of cells, use them to keep track of your inputs.


Gord Dibben MS Excel MVP

 

[Margo]

And I won't use either ... I did try it, and oh my goodness, did the columns
ever go wild.

 

[Margo]

Thank you. And after trying it anyway, I am heeding the warning.

Since I obviously have a lot to learn yet about formulas/functions I've
created a solution by 'Hide' the column with the original values after they
have been entered, my formulas then work well, and the column with the
correct and updated value is the only one showing. And 'Unhide' allows input
checking if necessary.

 

[Gord Dibben]

Good solution.

Thanks for the feedback.


Gord

Thank you. And after trying it anyway, I am heeding the warning.

Since I obviously have a lot to learn yet about formulas/functions I've
created a solution by 'Hide' the column with the original values after they
have been entered, my formulas then work well, and the column with the
correct and updated value is the only one showing. And 'Unhide' allows input
checking if necessary.