How torecord the highest value in a set of constantly changing num

[blue.jag]

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.

[Ron Coderre]

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.

[blue.jag]

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.

[Ron Coderre]

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.

[blue.jag]

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.

[Ron Coderre]

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.

[blue.jag]

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 B1>C1, 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.

[Ron Coderre]

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 B1>C1, 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.

[Ron Coderre]

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
Data>Data 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
File>Save 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 B1>C1, 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.

[Biff]

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 Tools>Options>Calculation
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" <[email protected]> wrote in message
news:[email protected]...
>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
> Data>Data 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
> File>Save 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 B1>C1, 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.

[blue.jag]

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 Tools>Options>Calculation
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > Data>Data 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
> > File>Save 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 B1>C1, 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.
>
>
>

[blue.jag]

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 Tools>Options>Calculation
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > Data>Data 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
> > File>Save 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 B1>C1, 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.
>
>
>