Excel Question (IF function).. another forum?

[luke242]

First, is there a larger specific excel forum out there?

Second, I am having trouble with the "IF" function. I want to have something happen to a cell if something else is true, put if It is not true, I want the cell to keep its current value.

Ex. Lets say A1 equals value "x" (..."x" is determined by a different formula) I want "x" to "y" if A1>A2. I try =IF(A1>A2,"y",A1). This gives me circular function error. if I use "" instead of A1, it clears A1, which I don't want to do. The value "x" is always different, so I can't use that. Basically, I don't want it to do anything to A1 if the IF function is false.

Please help,
_Luke Campbell

 

[DarkSun]

Okay,
you need to change the way it works.

I assume you are putting the Forumla in A1. that is why you are getting an error.

do this.

in A1 put =IF(yourformula>A2,"y",yourformula)

where yourformula is the formula for getting "x"

example x=D2+D3

therefore formula in A1 would be:

=IF((D2+D3)>A2,"y",(D2+D3))

 

[luke242]

Thank you for taking the time to answer.

Unfortunately, that will not work. Continuing with the same example...D2+D3 equals &quot;x&quot;, but the values in D2 and D3 are constantly changing, (they represent a stock price for today and yesterday, so tommorrow, it will be tommorrows price and todays price). I want &quot;x&quot; to be calculated only on the first day D2<D3, the first day the stock goes down. Subsequent days that D2<D3, I do not want &quot;x&quot; to be replaced because it would calculate &quot;x&quot; with D2 and D3 from the new day, not the first day the stock went down. I need the value of &quot;x&quot; calculated on the fisrt day of the drop only.

I have figured out a way to do this if I can tell the IF function to do nothing to A2 when it returns false.

Thank you for your help,
-Luke Campbell

 

[DarkSun]

The problem with that is when the values in d2 or d3 change excel has no way of knowing what the values were!

As far a I know the way to solve that problem is to keep a list of all the values eg. keep all the stock prices for each day and keep the result of the formula for x each day.

Then write some formulas to examine the data.

What is the exact problem you are trying to solve, is it stock price changes or something else?

 

[luke242]

Thank you for your help.. I need to think about it a little more.. I will probably post again in a couple days.

I am assuming there is no way to leave the cell unmodified using the IF function. Either you need to clear the cell with &quot;&quot; or you have to have IF function reference something else. There is no way to make the IF not modify the original cell (...not just imput the same value by using a previously used formula)

-Luke Campbell