-3

I have an excel sheet and I need to add an if statement to one of my cells. Let's call cell D13 as X and cell E13 as Y. Basically what I need to do is ensure that if X>=1 then Y = Y + (0.5*X). However, I understand that I cannot just say "E13 + 0.5*D13" since the cell is referencing itself and E13 is the value of the cell I need to change. I get a "circular reference error" when I do so. Is there a way I can use this same formula to ensure that my cell E13 is the value of itself plus D13 * 0.5? I do not want to put another cell and use this formula as it is redundant.

bumpfox
  • 17
  • 5
  • 1
    you can force excel to iterate, but you seem to have a proper circular reference that deos not converge onto a number! – Joop Oct 09 '15 at 09:54
  • 2
    Well, it's a circular reference. What did you expect? How can you fix it? You're saying "show me the value of the value plus five". That's never going to work. – Lightness Races in Orbit Oct 09 '15 at 10:07
  • Advice - before you attempt to code something (whether in a programming language or in Excel) you need to solve it conceptually. In this case, that means you need to solve it mathematically. How would you set up an example formula to solve what you are attempting? – Grade 'Eh' Bacon Oct 09 '15 at 13:03
  • "I do not want to put another cell and use this formula as it is redundant." Well it's only 'redundant' if its possible another way (which it isn't). But besides that, don't worry about using an extra cell or two in Excel; it is not a format which loves brevity. – Grade 'Eh' Bacon Oct 09 '15 at 13:10

3 Answers3

1

This sounds conceptually impossible, as the Y cell can't BOTH store a number and a formula that references that number. The very notion of "containing own value plus..." seems impossible in itself. I don't see any way around it but adding a third cell.

Peter Herdenborg
  • 5,736
  • 1
  • 20
  • 21
  • @bumpfox Don't be afraid to expand how many cells you use in Excel; It should not be treated like any other programming language, as brevity is often not possible, and actually increases complexity immensely. Learn to separate the calculation of what you are doing from the display of what you are doing, and allow the calculation of what you are doing to be as longwinded as you need it to be. – Grade 'Eh' Bacon Oct 09 '15 at 13:08
0

You need to re-think your processing logic. With formulas you are running into circular reference errors, as the comments above are demonstrating.

If you want to avoid the circular reference, you may need to involve VBA to do the calculation and write the result of the calculation into the cell. This can be done with a worksheet change event. It can monitor the cell, trigger when the cell changed, use the cell input value, perform a calculation and write the result into the same cell.

teylyn
  • 34,374
  • 4
  • 53
  • 73
0

In this case, I suggest you use 3 columns instead of two. First, you have D13 as X, and E13 as Y. But then you need to add F13 as Y2.

D13 pulls from wherever. E13 pulls from wherever. The formula in F13 is to check whether to pull the original value of Y, or a new one, like so:

=IF(D13>=1, E13+.5*D13,D13)

This says: if X >=1, make the new Y value in F13 equal to Y + .5 * X. Otherwise, make it equal to the original value of Y.

Then wherever you were going to reference E13 (the old "Y" value), reference F13 instead (which will pull whatever "Y" value is appropriate).

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46