Circular

M

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

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

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

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

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
 
M

Margo

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top