In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of data
for each day so that all days will be stored in the same worksheet? That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to structured
data and availability of historical workbooks. Can be particularly annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they are
always impacted by the same hardware problem: A loose nut in front of the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

Thank you Ron.
As you thought, the data won't be held after one week. Back in the early
'80s I had a spreadsheet with a "if .... then" function that would allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of data
for each day so that all days will be stored in the same worksheet? That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to structured
data and availability of historical workbooks. Can be particularly annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they are
always impacted by the same hardware problem: A loose nut in front of the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

First, a minor clarification...when you say "row", you mean "column", right?

So...going with that, and assuming each column contains a different day's
data...

Can't you just use something like this?:
A1: =MAX(A2:Z100)

That will return the maximum value in cells A2:Z100.
Of course, change the range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thank you Ron.
As you thought, the data won't be held after one week. Back in the early
'80s I had a spreadsheet with a "if .... then" function that would allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of data
for each day so that all days will be stored in the same worksheet? That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to structured
data and availability of historical workbooks. Can be particularly annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they are
always impacted by the same hardware problem: A loose nut in front of the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

I just realized you said that the data will not be older than 1 week.

Here are a couple ideas:
Could you have every week's sheet in the same workbook?
That would make things easier.
You could use a formula like: =MAX(Week1:Week52!$A$1)

or

Here's something that might work if you save each week's sheet:

A1: WeekMax
A2: =MAX(A3:E100,B2)
That will return the max of the largest value in A3:E100 OR the value in B2.

That workbook is saved as Week1.XLS.

Then, in the Week2.XLS file......
B1: WeekMax
B2: (blank)

Select B1:B2
DataData Consolidate
Browse to Week1.XLS and adjust to reference to be Week1.xls!$A$1:$A$2 and
add it to the consolidation ranges (and there'd be no links to break).

When you consolidate....the value in [Week1.xls]Sheet1!$A$2 will be loaded
into cell B2 in the Week2 workbook.

When you switch to Week3...
Start with Week2.xls
FileSave As: Week3.xls
Clear the data
Then, just edit the consolidation reference to point to Week2 (instead of
Week1)

There are other approaches, with varying degrees of
complexity/automation...but, right now, I'm trying to keep things relatively
simple and avoid linking to cells in another workbook that are linking to
cells in another workbook that are....(you get the point).

By the way, there are also forum members who already have automated programs
that would do what you want...so I'm just leaving it to them to chime in. No
point in me writing code that's already been written.

Any of this sound attractive?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First, a minor clarification...when you say "row", you mean "column", right?

So...going with that, and assuming each column contains a different day's
data...

Can't you just use something like this?:
A1: =MAX(A2:Z100)

That will return the maximum value in cells A2:Z100.
Of course, change the range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thank you Ron.
As you thought, the data won't be held after one week. Back in the early
'80s I had a spreadsheet with a "if .... then" function that would allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of data
for each day so that all days will be stored in the same worksheet? That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to structured
data and availability of historical workbooks. Can be particularly annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they are
always impacted by the same hardware problem: A loose nut in front of the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but you still want
to know the largest value that the range of numbers EVER had during the year?
Even though that number may not exist in the latest version of the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but when the column
of figures changes tomorrow and every day for the next 364 days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically) from a row of
numbers that changes each day. At the end of the year I still need to have
the highest number recorded over the whole year even though each previous
day's numbers disappear.

If the numbers are input into the same row range every day..........

Set up an intentional circular reference:

Assume the range of numbers is A1:E1.
New numbers are entered each day.

Assume the formula cell is G1.

Goto ToolsOptionsCalculation
Check Iteration
OK

Formula in G1:

=MAX(A1:E1,G1)

In order to avoid the circular reference warnings and popups you must first
set iteration before you create the formula.

A disadvantage to doing this is that there is no audit trail, and, if you
enter 1000 but meant to enter 100, ooops!

Biff

"Ron Coderre" wrote in message
...
I just realized you said that the data will not be older than 1 week.

Here are a couple ideas:
Could you have every week's sheet in the same workbook?
That would make things easier.
You could use a formula like: =MAX(Week1:Week52!$A$1)

or

Here's something that might work if you save each week's sheet:

A1: WeekMax
A2: =MAX(A3:E100,B2)
That will return the max of the largest value in A3:E100 OR the value in
B2.

That workbook is saved as Week1.XLS.

Then, in the Week2.XLS file......
B1: WeekMax
B2: (blank)

Select B1:B2
DataData Consolidate
Browse to Week1.XLS and adjust to reference to be Week1.xls!$A$1:$A$2 and
add it to the consolidation ranges (and there'd be no links to break).

When you consolidate....the value in [Week1.xls]Sheet1!$A$2 will be loaded
into cell B2 in the Week2 workbook.

When you switch to Week3...
Start with Week2.xls
FileSave As: Week3.xls
Clear the data
Then, just edit the consolidation reference to point to Week2 (instead of
Week1)

There are other approaches, with varying degrees of
complexity/automation...but, right now, I'm trying to keep things
relatively
simple and avoid linking to cells in another workbook that are linking to
cells in another workbook that are....(you get the point).

By the way, there are also forum members who already have automated
programs
that would do what you want...so I'm just leaving it to them to chime in.
No
point in me writing code that's already been written.

Any of this sound attractive?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First, a minor clarification...when you say "row", you mean "column",
right?

So...going with that, and assuming each column contains a different day's
data...

Can't you just use something like this?:
A1: =MAX(A2:Z100)

That will return the maximum value in cells A2:Z100.
Of course, change the range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thank you Ron.
As you thought, the data won't be held after one week. Back in the
early
'80s I had a spreadsheet with a "if .... then" function that would
allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells
and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else
C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of
data
for each day so that all days will be stored in the same worksheet?
That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to
structured
data and availability of historical workbooks. Can be particularly
annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they
are
always impacted by the same hardware problem: A loose nut in front of
the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus
is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but
you still want
to know the largest value that the range of numbers EVER had
during the year?
Even though that number may not exist in the latest version of
the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but
when the column
of figures changes tomorrow and every day for the next 364
days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and
then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically)
from a row of
numbers that changes each day. At the end of the year I
still need to have
the highest number recorded over the whole year even though
each previous
day's numbers disappear.

Thanks for the input everyone. I'll give your suggestion a try Biff

"Biff" wrote:

If the numbers are input into the same row range every day..........

Set up an intentional circular reference:

Assume the range of numbers is A1:E1.
New numbers are entered each day.

Assume the formula cell is G1.

Goto ToolsOptionsCalculation
Check Iteration
OK

Formula in G1:

=MAX(A1:E1,G1)

In order to avoid the circular reference warnings and popups you must first
set iteration before you create the formula.

A disadvantage to doing this is that there is no audit trail, and, if you
enter 1000 but meant to enter 100, ooops!

Biff

"Ron Coderre" wrote in message
...
I just realized you said that the data will not be older than 1 week.

Here are a couple ideas:
Could you have every week's sheet in the same workbook?
That would make things easier.
You could use a formula like: =MAX(Week1:Week52!$A$1)

or

Here's something that might work if you save each week's sheet:

A1: WeekMax
A2: =MAX(A3:E100,B2)
That will return the max of the largest value in A3:E100 OR the value in
B2.

That workbook is saved as Week1.XLS.

Then, in the Week2.XLS file......
B1: WeekMax
B2: (blank)

Select B1:B2
DataData Consolidate
Browse to Week1.XLS and adjust to reference to be Week1.xls!$A$1:$A$2 and
add it to the consolidation ranges (and there'd be no links to break).

When you consolidate....the value in [Week1.xls]Sheet1!$A$2 will be loaded
into cell B2 in the Week2 workbook.

When you switch to Week3...
Start with Week2.xls
FileSave As: Week3.xls
Clear the data
Then, just edit the consolidation reference to point to Week2 (instead of
Week1)

There are other approaches, with varying degrees of
complexity/automation...but, right now, I'm trying to keep things
relatively
simple and avoid linking to cells in another workbook that are linking to
cells in another workbook that are....(you get the point).

By the way, there are also forum members who already have automated
programs
that would do what you want...so I'm just leaving it to them to chime in.
No
point in me writing code that's already been written.

Any of this sound attractive?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First, a minor clarification...when you say "row", you mean "column",
right?

So...going with that, and assuming each column contains a different day's
data...

Can't you just use something like this?:
A1: =MAX(A2:Z100)

That will return the maximum value in cells A2:Z100.
Of course, change the range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thank you Ron.
As you thought, the data won't be held after one week. Back in the
early
'80s I had a spreadsheet with a "if .... then" function that would
allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells
and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else
C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of
data
for each day so that all days will be stored in the same worksheet?
That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to
structured
data and availability of historical workbooks. Can be particularly
annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they
are
always impacted by the same hardware problem: A loose nut in front of
the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus
is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but
you still want
to know the largest value that the range of numbers EVER had
during the year?
Even though that number may not exist in the latest version of
the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but
when the column
of figures changes tomorrow and every day for the next 364
days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and
then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically)
from a row of
numbers that changes each day. At the end of the year I
still need to have
the highest number recorded over the whole year even though
each previous
day's numbers disappear.

Thank you Biff. It works a treat. I've been looking for this answer for the
last 5 years and no-one seemed to know !!!

"Biff" wrote:

If the numbers are input into the same row range every day..........

Set up an intentional circular reference:

Assume the range of numbers is A1:E1.
New numbers are entered each day.

Assume the formula cell is G1.

Goto ToolsOptionsCalculation
Check Iteration
OK

Formula in G1:

=MAX(A1:E1,G1)

In order to avoid the circular reference warnings and popups you must first
set iteration before you create the formula.

A disadvantage to doing this is that there is no audit trail, and, if you
enter 1000 but meant to enter 100, ooops!

Biff

"Ron Coderre" wrote in message
...
I just realized you said that the data will not be older than 1 week.

Here are a couple ideas:
Could you have every week's sheet in the same workbook?
That would make things easier.
You could use a formula like: =MAX(Week1:Week52!$A$1)

or

Here's something that might work if you save each week's sheet:

A1: WeekMax
A2: =MAX(A3:E100,B2)
That will return the max of the largest value in A3:E100 OR the value in
B2.

That workbook is saved as Week1.XLS.

Then, in the Week2.XLS file......
B1: WeekMax
B2: (blank)

Select B1:B2
DataData Consolidate
Browse to Week1.XLS and adjust to reference to be Week1.xls!$A$1:$A$2 and
add it to the consolidation ranges (and there'd be no links to break).

When you consolidate....the value in [Week1.xls]Sheet1!$A$2 will be loaded
into cell B2 in the Week2 workbook.

When you switch to Week3...
Start with Week2.xls
FileSave As: Week3.xls
Clear the data
Then, just edit the consolidation reference to point to Week2 (instead of
Week1)

There are other approaches, with varying degrees of
complexity/automation...but, right now, I'm trying to keep things
relatively
simple and avoid linking to cells in another workbook that are linking to
cells in another workbook that are....(you get the point).

By the way, there are also forum members who already have automated
programs
that would do what you want...so I'm just leaving it to them to chime in.
No
point in me writing code that's already been written.

Any of this sound attractive?


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First, a minor clarification...when you say "row", you mean "column",
right?

So...going with that, and assuming each column contains a different day's
data...

Can't you just use something like this?:
A1: =MAX(A2:Z100)

That will return the maximum value in cells A2:Z100.
Of course, change the range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thank you Ron.
As you thought, the data won't be held after one week. Back in the
early
'80s I had a spreadsheet with a "if .... then" function that would
allow you
to keep the highest value that a row ever contained.

For example for a row A1..A99 you could first find the max of the cells
and
put it into (say) B1. Then in C1 you could say "if B1C1, then B1 else
C1".
So you always ended up with the maximum of any values entered into the
spreadsheet. But you can't do that in Exel.

Is there anything else like it in Exel ?? Or any other bright ideas.
Many thanks
blue.jag

"Ron Coderre" wrote:

OK...more questions (you saw that coming, right?)

By any chance, is it possible/practical to maintain a separate row of
data
for each day so that all days will be stored in the same worksheet?
That
would be the simplest approach. (I'm hoping for a YES on this one)

If NO, then the alternatives are much less attractive:

-Data Consolidate (Not exceptionally difficult, but is limited to
structured
data and availability of historical workbooks. Can be particularly
annoying
to maintain, but there are procedural workarounds to simplify the
effort...see my "Manual copy/paste_values comment, though)

-Linked workbooks (something I only do as a last resort)

-Manual copy/paste_values (I loathe "procedural" solutions....they
are
always impacted by the same hardware problem: A loose nut in front of
the
keyboard!)

-VBA processing of each day's data (somewhat of a mini-project...plus
is
there somebody who will understand/maintain the model?)

All are doable, though....so, let us know what you prefer.

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Yes please Ron.
Thank you in advance
Blue.jag

"Ron Coderre" wrote:

Are you saying that the list of numbers changes every day, but
you still want
to know the largest value that the range of numbers EVER had
during the year?
Even though that number may not exist in the latest version of
the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

Thanks Ron.
Your answer returns the maximum for the current column, but
when the column
of figures changes tomorrow and every day for the next 364
days, I still need
to retain one maximum figure over the whole 365 days.
Your suggestion only gives me the max one day at a time and
then that max is
gone the next day!

"Ron Coderre" wrote:

If the numbers are in Row 1:

B2: =MAX(1:1)
OR
B2: =MAX(A1:Z1)

If the numbers are in Col_A:

B2: =MAX(A:A)
OR
B2: =MAX(A1:A100)

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blue.jag" wrote:

In Exel, how can I record the highest value (automatically)
from a row of
numbers that changes each day. At the end of the year I
still need to have
the highest number recorded over the whole year even though
each previous
day's numbers disappear.