in B1, =IF(A1>B1, A1, B1)

[Lamb Chop]

HOw to change max in a cell automatically



A1=some value that I will change all the time.

B1= another value

I want to Change B1 to A1 if A1 >B1 otherwise B1 keep to its value.

In B1, I have

= if(A1>B1, A1, B1)

I got error. Any suggestion?

Thanks

 

[JLatham]

You're creating a circular reference, that's the error you are getting. You
need to get whatever value you want to start with in B1 either into it or
another cell and then use another similar IF statement elsewhere.

If you want to continue to use B1 as the cell for some input, then over in
C1 you could use
=IF(A1>B1,A1,B1)

 

[MartinW]

Hi Lamb Chop,

If B1 is a fixed value you could use
=MAX(A1,your fixed value)

HTH
Martin

 

[Lamb Chop]

I think I probably did not post my question clearly.

I have a spreadsheet for shares. Everyday, it may have a new record of
highest price. I store this record in B1 and I also record the latest price
to A1. A1 may or may not be the maximum. I did some calculation based on
A1 and B1.

Most of the time, I only change the value in A1. But if A1 is bigger than
the old B1, then I need to manually change B1 so that B1 always reflects the
maximum value of the stock.

I am wondering if there is a way, B1 can check if A1 is bigger than the
value B1 is having. If yes, then it change B1 to A1, else do nothing.
That is why I have,

B1: = if (A1>B1, A1, leave the old value stayed in B1)



Thanks

 

[Bernard Liengme]

No you cannot do this without VBA
You need to keep the two values in A1 and B1 and use C1 to find the max with
=MAX(A1,B1)
Now do your calculations using C1
best wishes

 

[MartinW]

Hi Bernard,

I got Lamb Chop's circular reference to work by going to
Tools>Options>Calculation Tab and checking iteration
So B1 =IF(A1>B1,A1,B1) will now do what he is trying to do.

But that raises a couple of questions.
1) What are the pitfalls of doing this?
2) How does the maximum number of iterations affect
the process? It doesn't seem to make any difference whether
it is set to 1 or 10000.

Regards
Martin

 

[Lamb Chop]

I have tried this, Martin.

using iternation is only good for update the value one time. If you look at
the equation in B1, it changes to a value instead of keeping itself as a
formula. If I keep inputing different value in A1, B1 will not change
accordingly.

Thanks for you guys help. Perhaps, it is a good time to learn VBA. Do you
guys know how to run the script in the background once A1 has change without
the need of pressing any other buttoms?

Thanks

 

[MartinW]

It's working here Lamb Chop. I've changed the value in A1
about 100 times changing it up and down. B1 holds on whatever
the maximum is until a new maximum is reached. The formula
remains intact.

I just went back and applied a spinner to A1 so that I could
run the value up and down quicker and it is still working
fine.

HTH
Martin

 

[MartinW]

Hi again,

The only way I can get it to not change is if calculation
is set to manual in which case you have to tap F9 to
update B1.

HTH
Martin

 

[Lamb Chop]

Thanks Martin
I must have made another stupid mistake.

 

[MartinW]

Hi Lamb Chop,

I'm sailing in unchartered waters here myself.
I'd like to know if it is doing what you wanted and
I'd also like an answer from Bernard but he's most
likely lost track with this thread by now.

Regards
Martin