Maximum Value of a Cell over a series of calculations

K

Kypp

Basically this is what I am trying to accomplish:

Column #1 -
This column will contain a formula that updates when an variable cell
value is changed elsewhere in the spreadsheet.

Column #2 -
I would like this column to update and display the value in Column #1
ONLY when the value in Column #1 is greater than the current value in
Column #2.

This should produce the maximum value over a long series of
calculations as I change a variable. The tricky part is I would like
to do this over a range of 300 cells without typing in a ton of VBA
code. If anyone has any suggestions please let me know.

Thanks,

Bryan
 
F

Franz Verga

Kypp said:
Basically this is what I am trying to accomplish:

Column #1 -
This column will contain a formula that updates when an variable cell
value is changed elsewhere in the spreadsheet.

Column #2 -
I would like this column to update and display the value in Column #1
ONLY when the value in Column #1 is greater than the current value in
Column #2.

This should produce the maximum value over a long series of
calculations as I change a variable. The tricky part is I would like
to do this over a range of 300 cells without typing in a ton of VBA
code. If anyone has any suggestions please let me know.

Thanks,

Bryan
Click to expand...


Hi Bryan,

Because you don't want to use VBA, I think you need a third column because a
cell can have or a formula or a value, not both depending on a condition.

So if we assume you use column A, B and C, starting from row 2 (I assume you
have headers in row 1), you will have your formula in column A, some values
in column B and you can type this formula in C2:

=IF(A2>B2,A2,B2)

Now you can drag the cell C2 across the row. If you want you can also hide
the column B with the fixed values.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
K

Kypp

Franz,

Thanks for your help. This isn't quite what I was looking for though
since the values that I am comparing Column A to are not fixed values
but all the previous values (really just the last iteration) of Column
A. Recalculating the previous iteration of Column A would and
displaying it in Column B would work as you discribed but that is a lot
more spreadsheet to write and woulnd end up complicating things even
more.

When I wrote this question I had a way of doing it in VBA it just
required 300 lines of code for each comparison that I wanted to do and
it also required a bit of computer muscle. I have since figured out
how to do it in about 10 lines of code for each comparison but it still
slows down my program and forces me to disable screen updating. This
isn't so bad except that it would be useful to be able to animate the
problem I am analysing in realtime. So I am not opposed to VBA just
trying to streamline the code so that it runs more smoothly
 
Top