Formula to replace contents of a cell

    [FONT=&quot]I have a value in a cell, say 273.75 in A1. Everyday, I download a data into this cell.[/FONT]
    [FONT=&quot]I have another cell with value 275.00 in B1.[/FONT]
    [FONT=&quot]Now, whenever in future, the downloaded value in A1 exceeds 275 say 278, I want B1 to change to 278 automatically. Can we do this by writing a formula in the relevant cell?[/FONT]

    Re: Formula to replace contents of a cell


    Your question is vague.


    If A1 > 275 then is B1 set to the value of A1 or a fixed value of 278?. What if, the next day. A1 changes to 281? What happens if it goes below 275?

    Re: Formula to replace contents of a cell


    Let me clarify. Value in cell A1 is the price of a stock. Value in cell B1 is all time high share price of the same stock. This cell A1 gets updated at 7 PM everyday. Now if cell A1 increases when we update the data at 7 pm, I want cell B1 should show that new price. But if it decreases or remains the same, then B1 does not change. In other words cell B1 always shows highest price of the stock. Hope I have explained enough. Secondly, if I do manually everyday, I can replace by comparing A1 and B1. But my question is can we do it automatically, by writing a formula in cell B1 or elsewhere?

    Re: Formula to replace contents of a cell


    Thank you for the clarification - that would have been a good post if it had been your first in the thread


    Yes, it can be done with a formula, but as that formula will reference the cell containing the value it will cause an Iterative Calculation.


    [bfn]=MAX(A1, B1)[/bfn]in Cell B1.


    Excel by default will not calculate cells with iterative references so you will have to change the Options/Formulas setting to allow Iterative Calculations setting the maximum number of iterations to 1.


    Iterative references are not a good thing - setting that option on can mask other, unintentional, iterative calculations. This type of error is very difficult to find so you should audit your worksheet carefully before enabling iterative calculations.


    As an aside, and as long as A1 is updated manually or by code (I.E. not as a result of a formula in the cell), then this functionality is simple to implement in VBA, but this is posted in EXCEL FORMULAS so I'll assume that's not an option.

    Re: Formula to replace contents of a cell


    Thanks for giving a solution. One doubt. There are several stocks. Some stocks are quoting at very low prices today.
    Take this example to understand.
    One stock X is priced 23 today. And high price was 67. So B1 is 67 as of now. It would be at least a year for A1 to cross 67. As long is A1 is below 67, B1 should not change. I presume with your suggestion of MAX command this would happen. I hope I am clear to you. Can you suggest a variation to your command or some other formula?


    As you have said correctly my first choice is by way of formula. If not, how I can transfer my query to VB page?

    Re: Formula to replace contents of a cell


    Quote

    I presume ... this would happen


    Why don't you test it?


    The formula will only record the max known value. If a stock was previously at a higher value then you are going to have to enter that manually first to 'seed' the historical high values before you start updates .

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login