hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest possible
sum, but within a 12 month period. in the above example, I know that
the sums from 1st of October 2004 until 1st of October 2005 are 191$.
except I have to do this manually, and don't know whether for instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

may not be exactly what youre after but if you sort by col a then col b(or
even col c after you have split the year out)you will get the maximum for
each year at the top of each years listings
--
paul

remove nospam for email addy!



" wrote:

hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest possible
sum, but within a 12 month period. in the above example, I know that
the sums from 1st of October 2004 until 1st of October 2005 are 191$.
except I have to do this manually, and don't know whether for instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi

I put my start date in F1 and my end date in G1 then the array entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest possible
sum, but within a 12 month period. in the above example, I know that
the sums from 1st of October 2004 until 1st of October 2005 are 191$.
except I have to do this manually, and don't know whether for instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

What does

*--

mean?

Roger Govier wrote:
Hi

I put my start date in F1 and my end date in G1 then the array entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

Roger,

Thanks for your reply. Unfortunately, the thing does not seem to work.
Could you explain in any more detail as to how the formula is supposed
to work?

As said before, the starting sum is in A2 and the end sum is in A123.
The corresponding dates for each sum are from B2 to B123. Just to
clarify, I would like to find out what the maximum sum is over a 12
month period - any 12 month period. The problem with this is that the
dates, as you can see in my original post, are not incremental, ie 1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range (say, if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum all the
cells between A5 and A118. The problem with this is it is a pain to go
through all possible variants.... ie 10 October 2005 to 9 October 2006,
then 11 October 2005 to 10 October 2006, etc..... (as said before, the
date increments are random - so it is not always from a specific date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest possible
sum, but within a 12 month period. in the above example, I know that
the sums from 1st of October 2004 until 1st of October 2005 are 191$.
except I have to do this manually, and don't know whether for instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi

It may be because you have headers, and when I tied it first I omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to convert
your text values in column A from the cut and paste I made of your data.
I amended out the formula before posting, but omitted to delete the
double unary which is superfluous (even though it didn't effect the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you had
just typed the $ sign when posting, if your data has a trailing $ is
will not work.
Your dates may not be true Excel dates - well they couldn't be if what
you posted was from your data, as there is no date of 30 February 2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2 etc and
copy the formula down.

If you are still having difficulties, post back with your real email
address and I will send you a sample workbook with it working. You could
also achieve what you want with a Pivot Table, again post back if you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to work.
Could you explain in any more detail as to how the formula is supposed
to work?

As said before, the starting sum is in A2 and the end sum is in A123.
The corresponding dates for each sum are from B2 to B123. Just to
clarify, I would like to find out what the maximum sum is over a 12
month period - any 12 month period. The problem with this is that the
dates, as you can see in my original post, are not incremental, ie 1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range (say, if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum all the
cells between A5 and A118. The problem with this is it is a pain to go
through all possible variants.... ie 10 October 2005 to 9 October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said before, the
date increments are random - so it is not always from a specific date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I know
that
the sums from 1st of October 2004 until 1st of October 2005 are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi Aladin

Totally superfluous double unary minus "--". The multiplication alone
will coerce the True's and |False's to 1's and 0's.

I had used a formula to convert the source data from text to numeric,
and forgot to omit the double unary when amending the formula for
posting.

--
Regards

Roger Govier


"Aladin Akyurek" wrote in message
...
What does

*--

mean?

Roger Govier wrote:
Hi

I put my start date in F1 and my end date in G1 then the array
entered formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved is way
lower than what I can calculate on my own by just summing up the sums
from a 12 month period. I would be grateful if you could send me a copy
of those workbooks, my email is the same one I am posting from, I will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to convert
your text values in column A from the cut and paste I made of your data.
I amended out the formula before posting, but omitted to delete the
double unary which is superfluous (even though it didn't effect the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you had
just typed the $ sign when posting, if your data has a trailing $ is
will not work.
Your dates may not be true Excel dates - well they couldn't be if what
you posted was from your data, as there is no date of 30 February 2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2 etc and
copy the formula down.

If you are still having difficulties, post back with your real email
address and I will send you a sample workbook with it working. You could
also achieve what you want with a Pivot Table, again post back if you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to work.
Could you explain in any more detail as to how the formula is supposed
to work?

As said before, the starting sum is in A2 and the end sum is in A123.
The corresponding dates for each sum are from B2 to B123. Just to
clarify, I would like to find out what the maximum sum is over a 12
month period - any 12 month period. The problem with this is that the
dates, as you can see in my original post, are not incremental, ie 1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range (say, if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum all the
cells between A5 and A118. The problem with this is it is a pain to go
through all possible variants.... ie 10 October 2005 to 9 October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said before, the
date increments are random - so it is not always from a specific date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how to
identify the greatest possible sum from a column, within a 12 month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns (example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I know
that
the sums from 1st of October 2004 until 1st of October 2005 are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will give me a
bigger sum. is there any way to do this in excel?

anyone? :)

Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved is way
lower than what I can calculate on my own by just summing up the sums
from a 12 month period. I would be grateful if you could send me a
copy
of those workbooks, my email is the same one I am posting from, I will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to convert
your text values in column A from the cut and paste I made of your
data.
I amended out the formula before posting, but omitted to delete the
double unary which is superfluous (even though it didn't effect the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you had
just typed the $ sign when posting, if your data has a trailing $ is
will not work.
Your dates may not be true Excel dates - well they couldn't be if
what
you posted was from your data, as there is no date of 30 February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2 etc
and
copy the formula down.

If you are still having difficulties, post back with your real email
address and I will send you a sample workbook with it working. You
could
also achieve what you want with a Pivot Table, again post back if you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is in
A123.
The corresponding dates for each sum are from B2 to B123. Just to
clarify, I would like to find out what the maximum sum is over a 12
month period - any 12 month period. The problem with this is that
the
dates, as you can see in my original post, are not incremental, ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range (say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum all
the
cells between A5 and A118. The problem with this is it is a pain to
go
through all possible variants.... ie 10 October 2005 to 9 October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said before,
the
date increments are random - so it is not always from a specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how
to
identify the greatest possible sum from a column, within a 12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I know
that
the sums from 1st of October 2004 until 1st of October 2005 are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Roger,

Unfortunately nothing arrived - I have emailed you directly (excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved is way
lower than what I can calculate on my own by just summing up the sums
from a 12 month period. I would be grateful if you could send me a
copy
of those workbooks, my email is the same one I am posting from, I will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to convert
your text values in column A from the cut and paste I made of your
data.
I amended out the formula before posting, but omitted to delete the
double unary which is superfluous (even though it didn't effect the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you had
just typed the $ sign when posting, if your data has a trailing $ is
will not work.
Your dates may not be true Excel dates - well they couldn't be if
what
you posted was from your data, as there is no date of 30 February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2 etc
and
copy the formula down.

If you are still having difficulties, post back with your real email
address and I will send you a sample workbook with it working. You
could
also achieve what you want with a Pivot Table, again post back if you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is in
A123.
The corresponding dates for each sum are from B2 to B123. Just to
clarify, I would like to find out what the maximum sum is over a 12
month period - any 12 month period. The problem with this is that
the
dates, as you can see in my original post, are not incremental, ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range (say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum all
the
cells between A5 and A118. The problem with this is it is a pain to
go
through all possible variants.... ie 10 October 2005 to 9 October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said before,
the
date increments are random - so it is not always from a specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to how
to
identify the greatest possible sum from a column, within a 12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I know
that
the sums from 1st of October 2004 until 1st of October 2005 are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi

Picked the email up this morning, and sent direct to your email address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly (excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved is
way
lower than what I can calculate on my own by just summing up the
sums
from a 12 month period. I would be grateful if you could send me a
copy
of those workbooks, my email is the same one I am posting from, I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to
convert
your text values in column A from the cut and paste I made of your
data.
I amended out the formula before posting, but omitted to delete
the
double unary which is superfluous (even though it didn't effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you
had
just typed the $ sign when posting, if your data has a trailing $
is
will not work.
Your dates may not be true Excel dates - well they couldn't be if
what
you posted was from your data, as there is no date of 30 February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your real
email
address and I will send you a sample workbook with it working. You
could
also achieve what you want with a Pivot Table, again post back if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is in
A123.
The corresponding dates for each sum are from B2 to B123. Just
to
clarify, I would like to find out what the maximum sum is over a
12
month period - any 12 month period. The problem with this is
that
the
dates, as you can see in my original post, are not incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum
all
the
cells between A5 and A118. The problem with this is it is a pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to
how
to
identify the greatest possible sum from a column, within a 12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I
know
that
the sums from 1st of October 2004 until 1st of October 2005
are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Roger,

Many thanks - I received your email this time round. Unfortunately I
think there has been a misunderstanding, and re-reading my original
post I think it was misunderstood (maybe I wasn't clear enough or just
wrongly described the problem). What the worksheet/formula does is find
*one* sum, the maximum/highest sum, within a 12 month period. However
what I am looking for is a way to find the highest possible total sum
from any 12 month period - by adding all the individual sums from
within that 12 month period.

That is my problem... apologies once again for maybe not making this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly (excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved is
way
lower than what I can calculate on my own by just summing up the
sums
from a 12 month period. I would be grateful if you could send me a
copy
of those workbooks, my email is the same one I am posting from, I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1 e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to
convert
your text values in column A from the cut and paste I made of your
data.
I amended out the formula before posting, but omitted to delete
the
double unary which is superfluous (even though it didn't effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume you
had
just typed the $ sign when posting, if your data has a trailing $
is
will not work.
Your dates may not be true Excel dates - well they couldn't be if
what
you posted was from your data, as there is no date of 30 February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see the
difference in the result., or put further sets of dates in G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your real
email
address and I will send you a sample workbook with it working. You
could
also achieve what you want with a Pivot Table, again post back if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is in
A123.
The corresponding dates for each sum are from B2 to B123. Just
to
clarify, I would like to find out what the maximum sum is over a
12
month period - any 12 month period. The problem with this is
that
the
dates, as you can see in my original post, are not incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then sum
all
the
cells between A5 and A118. The problem with this is it is a pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as to
how
to
identify the greatest possible sum from a column, within a 12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the greatest
possible
sum, but within a 12 month period. in the above example, I
know
that
the sums from 1st of October 2004 until 1st of October 2005
are
191$.
except I have to do this manually, and don't know whether for
instance
checking between 1 December 2004 and 1 December 2005 will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi

Well, in that case, use the sheet I sent you, but use the following
formula in cell D2
=SUMPRODUCT(($B$2:$B$15=$F1)*
($B$2:$B$15<=$G1)*($A$2:$A$15))
Copy down through cells D3:E3
This will give the totals for the 3 calendar years beginning 01 Jan
2004.

Alternatively, just double click on the Filed in the Pivot Table that
says Max of Amounts, and from the dropdown select Sum.

If you wanted the values for a range of 12 month periods beginning with
the start dates in column B, and extending for a period of 1 year from
that date, then enter the following in cell D2 and copy down to cell D15
=SUMPRODUCT(($B$2:$B$15=B2)*
($B$2:$B$15<=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))*
($A$2:$A$15))

I obtained values as shown below

191 291 286 273 244 221 216 176 163 139 140 1020 993 1007

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Many thanks - I received your email this time round. Unfortunately I
think there has been a misunderstanding, and re-reading my original
post I think it was misunderstood (maybe I wasn't clear enough or just
wrongly described the problem). What the worksheet/formula does is
find
*one* sum, the maximum/highest sum, within a 12 month period. However
what I am looking for is a way to find the highest possible total sum
from any 12 month period - by adding all the individual sums from
within that 12 month period.

That is my problem... apologies once again for maybe not making this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email
address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly
(excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved
is
way
lower than what I can calculate on my own by just summing up the
sums
from a 12 month period. I would be grateful if you could send me
a
copy
of those workbooks, my email is the same one I am posting from,
I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered
formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1
e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in
my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to
convert
your text values in column A from the cut and paste I made of
your
data.
I amended out the formula before posting, but omitted to delete
the
double unary which is superfluous (even though it didn't effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes
on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume
you
had
just typed the $ sign when posting, if your data has a trailing
$
is
will not work.
Your dates may not be true Excel dates - well they couldn't be
if
what
you posted was from your data, as there is no date of 30
February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array
formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see
the
difference in the result., or put further sets of dates in
G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your real
email
address and I will send you a sample workbook with it working.
You
could
also achieve what you want with a Pivot Table, again post back
if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem
to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is
in
A123.
The corresponding dates for each sum are from B2 to B123.
Just
to
clarify, I would like to find out what the maximum sum is
over a
12
month period - any 12 month period. The problem with this is
that
the
dates, as you can see in my original post, are not
incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then
sum
all
the
cells between A5 and A118. The problem with this is it is a
pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a
specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the
array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as
to
how
to
identify the greatest possible sum from a column, within a
12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the
greatest
possible
sum, but within a 12 month period. in the above example, I
know
that
the sums from 1st of October 2004 until 1st of October
2005
are
191$.
except I have to do this manually, and don't know whether
for
instance
checking between 1 December 2004 and 1 December 2005 will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Roger, you are genius :) - I wanted the second thing and now it works
perfectly - you've saved me lots of hours! A big thank you to you!

Roger Govier schrieb:

Hi

Well, in that case, use the sheet I sent you, but use the following
formula in cell D2
=SUMPRODUCT(($B$2:$B$15=$F1)*
($B$2:$B$15<=$G1)*($A$2:$A$15))
Copy down through cells D3:E3
This will give the totals for the 3 calendar years beginning 01 Jan
2004.

Alternatively, just double click on the Filed in the Pivot Table that
says Max of Amounts, and from the dropdown select Sum.

If you wanted the values for a range of 12 month periods beginning with
the start dates in column B, and extending for a period of 1 year from
that date, then enter the following in cell D2 and copy down to cell D15
=SUMPRODUCT(($B$2:$B$15=B2)*
($B$2:$B$15<=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))*
($A$2:$A$15))

I obtained values as shown below

191 291 286 273 244 221 216 176 163 139 140 1020 993 1007

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Many thanks - I received your email this time round. Unfortunately I
think there has been a misunderstanding, and re-reading my original
post I think it was misunderstood (maybe I wasn't clear enough or just
wrongly described the problem). What the worksheet/formula does is
find
*one* sum, the maximum/highest sum, within a 12 month period. However
what I am looking for is a way to find the highest possible total sum
from any 12 month period - by adding all the individual sums from
within that 12 month period.

That is my problem... apologies once again for maybe not making this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email
address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly
(excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is achieved
is
way
lower than what I can calculate on my own by just summing up the
sums
from a 12 month period. I would be grateful if you could send me
a
copy
of those workbooks, my email is the same one I am posting from,
I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first I
omitted
the headers.
Assuming your data starts in row 2 then the array entered
formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in G1
e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as in
my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula to
convert
your text values in column A from the cut and paste I made of
your
data.
I amended out the formula before posting, but omitted to delete
the
double unary which is superfluous (even though it didn't effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see notes
on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I assume
you
had
just typed the $ sign when posting, if your data has a trailing
$
is
will not work.
Your dates may not be true Excel dates - well they couldn't be
if
what
you posted was from your data, as there is no date of 30
February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array
formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see
the
difference in the result., or put further sets of dates in
G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your real
email
address and I will send you a sample workbook with it working.
You
could
also achieve what you want with a Pivot Table, again post back
if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not seem
to
work.
Could you explain in any more detail as to how the formula is
supposed
to work?

As said before, the starting sum is in A2 and the end sum is
in
A123.
The corresponding dates for each sum are from B2 to B123.
Just
to
clarify, I would like to find out what the maximum sum is
over a
12
month period - any 12 month period. The problem with this is
that
the
dates, as you can see in my original post, are not
incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then
sum
all
the
cells between A5 and A118. The problem with this is it is a
pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a
specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the
array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers as
to
how
to
identify the greatest possible sum from a column, within a
12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the
greatest
possible
sum, but within a 12 month period. in the above example, I
know
that
the sums from 1st of October 2004 until 1st of October
2005
are
191$.
except I have to do this manually, and don't know whether
for
instance
checking between 1 December 2004 and 1 December 2005 will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi
You are very welcome. Thanks for the feedback and letting me know this
has solved your problem.

--
Regards

Roger Govier


wrote in message
oups.com...
Roger, you are genius :) - I wanted the second thing and now it works
perfectly - you've saved me lots of hours! A big thank you to you!

Roger Govier schrieb:

Hi

Well, in that case, use the sheet I sent you, but use the following
formula in cell D2
=SUMPRODUCT(($B$2:$B$15=$F1)*
($B$2:$B$15<=$G1)*($A$2:$A$15))
Copy down through cells D3:E3
This will give the totals for the 3 calendar years beginning 01 Jan
2004.

Alternatively, just double click on the Filed in the Pivot Table that
says Max of Amounts, and from the dropdown select Sum.

If you wanted the values for a range of 12 month periods beginning
with
the start dates in column B, and extending for a period of 1 year
from
that date, then enter the following in cell D2 and copy down to cell
D15
=SUMPRODUCT(($B$2:$B$15=B2)*
($B$2:$B$15<=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))*
($A$2:$A$15))

I obtained values as shown below

191 291 286 273 244 221 216 176 163 139 140 1020 993 1007

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Many thanks - I received your email this time round. Unfortunately
I
think there has been a misunderstanding, and re-reading my original
post I think it was misunderstood (maybe I wasn't clear enough or
just
wrongly described the problem). What the worksheet/formula does is
find
*one* sum, the maximum/highest sum, within a 12 month period.
However
what I am looking for is a way to find the highest possible total
sum
from any 12 month period - by adding all the individual sums from
within that 12 month period.

That is my problem... apologies once again for maybe not making
this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email
address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly
(excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is
achieved
is
way
lower than what I can calculate on my own by just summing up
the
sums
from a 12 month period. I would be grateful if you could send
me
a
copy
of those workbooks, my email is the same one I am posting
from,
I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first
I
omitted
the headers.
Assuming your data starts in row 2 then the array entered
formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in
G1
e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as
in
my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula
to
convert
your text values in column A from the cut and paste I made
of
your
data.
I amended out the formula before posting, but omitted to
delete
the
double unary which is superfluous (even though it didn't
effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see
notes
on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I
assume
you
had
just typed the $ sign when posting, if your data has a
trailing
$
is
will not work.
Your dates may not be true Excel dates - well they couldn't
be
if
what
you posted was from your data, as there is no date of 30
February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array
formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see
the
difference in the result., or put further sets of dates in
G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your
real
email
address and I will send you a sample workbook with it
working.
You
could
also achieve what you want with a Pivot Table, again post
back
if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not
seem
to
work.
Could you explain in any more detail as to how the formula
is
supposed
to work?

As said before, the starting sum is in A2 and the end sum
is
in
A123.
The corresponding dates for each sum are from B2 to B123.
Just
to
clarify, I would like to find out what the maximum sum is
over a
12
month period - any 12 month period. The problem with this
is
that
the
dates, as you can see in my original post, are not
incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month
range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then
sum
all
the
cells between A5 and A118. The problem with this is it is
a
pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a
specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the
array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you
use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers
as
to
how
to
identify the greatest possible sum from a column,
within a
12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the
greatest
possible
sum, but within a 12 month period. in the above
example, I
know
that
the sums from 1st of October 2004 until 1st of October
2005
are
191$.
except I have to do this manually, and don't know
whether
for
instance
checking between 1 December 2004 and 1 December 2005
will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Just when I thought I had finally solved the problem, I noticed one
more thing:
as the numbers come to an end, the formula does not work anymore. The
result is true up to the point that there is a corresponding value with
a date 1 year in advance - if there is not, it is simply not counted
in.

Thus, if my last sum is, say, 10$ on the 10 Sept 2006, then the
formula, pasted in as you suggested, will only show a total of 10$ -
eventhough there are values preceeding it in the past 12 months, and it
should show a maximum for the period 10 Sept 2005 to 10 Sept 2006 (and,
instead, it is showing 10 Sept 2006 to 10 Sept 2007... which cannot
work of course, as there are no values for that time period). It works
perfectly fine up to a date that has a value in 12 months - once that
is gone, it just simply decreases, instead of looking backwards to
count those 12 months.

Any suggestions? If I had a clearer explanation of the formula I could
probably work it out myself, but unfortunately I am not such an excel
expert. Basically I would need something that takes into account the
fact to switch to look backwards for 12 months and not forwards, once
the end sum date is reached, or the same formula as before but in
reverse, taking the sums and adding the last 12 months, instead of the
12 months ahead, I can figure out the rest myself...



Roger Govier schrieb:

Hi
You are very welcome. Thanks for the feedback and letting me know this
has solved your problem.

--
Regards

Roger Govier


wrote in message
oups.com...
Roger, you are genius :) - I wanted the second thing and now it works
perfectly - you've saved me lots of hours! A big thank you to you!

Roger Govier schrieb:

Hi

Well, in that case, use the sheet I sent you, but use the following
formula in cell D2
=SUMPRODUCT(($B$2:$B$15=$F1)*
($B$2:$B$15<=$G1)*($A$2:$A$15))
Copy down through cells D3:E3
This will give the totals for the 3 calendar years beginning 01 Jan
2004.

Alternatively, just double click on the Filed in the Pivot Table that
says Max of Amounts, and from the dropdown select Sum.

If you wanted the values for a range of 12 month periods beginning
with
the start dates in column B, and extending for a period of 1 year
from
that date, then enter the following in cell D2 and copy down to cell
D15
=SUMPRODUCT(($B$2:$B$15=B2)*
($B$2:$B$15<=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))*
($A$2:$A$15))

I obtained values as shown below

191 291 286 273 244 221 216 176 163 139 140 1020 993 1007

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Many thanks - I received your email this time round. Unfortunately
I
think there has been a misunderstanding, and re-reading my original
post I think it was misunderstood (maybe I wasn't clear enough or
just
wrongly described the problem). What the worksheet/formula does is
find
*one* sum, the maximum/highest sum, within a 12 month period.
However
what I am looking for is a way to find the highest possible total
sum
from any 12 month period - by adding all the individual sums from
within that 12 month period.

That is my problem... apologies once again for maybe not making
this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email
address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly
(excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is
achieved
is
way
lower than what I can calculate on my own by just summing up
the
sums
from a 12 month period. I would be grateful if you could send
me
a
copy
of those workbooks, my email is the same one I am posting
from,
I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it first
I
omitted
the headers.
Assuming your data starts in row 2 then the array entered
formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date in
G1
e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign as
in
my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a formula
to
convert
your text values in column A from the cut and paste I made
of
your
data.
I amended out the formula before posting, but omitted to
delete
the
double unary which is superfluous (even though it didn't
effect
the
result.)

You may not be getting it to work for a number of reasons :-
You may have omitted to enter it as an array formula (see
notes
on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I
assume
you
had
just typed the $ sign when posting, if your data has a
trailing
$
is
will not work.
Your dates may not be true Excel dates - well they couldn't
be
if
what
you posted was from your data, as there is no date of 30
February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array
formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and see
the
difference in the result., or put further sets of dates in
G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your
real
email
address and I will send you a sample workbook with it
working.
You
could
also achieve what you want with a Pivot Table, again post
back
if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does not
seem
to
work.
Could you explain in any more detail as to how the formula
is
supposed
to work?

As said before, the starting sum is in A2 and the end sum
is
in
A123.
The corresponding dates for each sum are from B2 to B123.
Just
to
clarify, I would like to find out what the maximum sum is
over a
12
month period - any 12 month period. The problem with this
is
that
the
dates, as you can see in my original post, are not
incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month
range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and then
sum
all
the
cells between A5 and A118. The problem with this is it is
a
pain
to
go
through all possible variants.... ie 10 October 2005 to 9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as said
before,
the
date increments are random - so it is not always from a
specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then the
array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if you
use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any pointers
as
to
how
to
identify the greatest possible sum from a column,
within a
12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the
greatest
possible
sum, but within a 12 month period. in the above
example, I
know
that
the sums from 1st of October 2004 until 1st of October
2005
are
191$.
except I have to do this manually, and don't know
whether
for
instance
checking between 1 December 2004 and 1 December 2005
will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Hi

The formula I posted dealt with fixed dates for any period of time that
you entered where start sate was in column F and end date in column G.
You therefore have control over what time periods you want the data
summed.

If you are now saying that you want to know the total amount for every
row, for the period of 12 months ending on the date for that particular
transaction, then in cell C2 of the file I sent you, enter the following
formula and copy down

=SUMPRODUCT(($B$2:$B$15=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2)+1))
*($B$2:$B$15<=B2)*($A$2:$A$15))

--
Regards

Roger Govier


wrote in message
oups.com...
Just when I thought I had finally solved the problem, I noticed one
more thing:
as the numbers come to an end, the formula does not work anymore. The
result is true up to the point that there is a corresponding value
with
a date 1 year in advance - if there is not, it is simply not counted
in.

Thus, if my last sum is, say, 10$ on the 10 Sept 2006, then the
formula, pasted in as you suggested, will only show a total of 10$ -
eventhough there are values preceeding it in the past 12 months, and
it
should show a maximum for the period 10 Sept 2005 to 10 Sept 2006
(and,
instead, it is showing 10 Sept 2006 to 10 Sept 2007... which cannot
work of course, as there are no values for that time period). It works
perfectly fine up to a date that has a value in 12 months - once that
is gone, it just simply decreases, instead of looking backwards to
count those 12 months.

Any suggestions? If I had a clearer explanation of the formula I could
probably work it out myself, but unfortunately I am not such an excel
expert. Basically I would need something that takes into account the
fact to switch to look backwards for 12 months and not forwards, once
the end sum date is reached, or the same formula as before but in
reverse, taking the sums and adding the last 12 months, instead of the
12 months ahead, I can figure out the rest myself...



Roger Govier schrieb:

Hi
You are very welcome. Thanks for the feedback and letting me know
this
has solved your problem.

--
Regards

Roger Govier


wrote in message
oups.com...
Roger, you are genius :) - I wanted the second thing and now it
works
perfectly - you've saved me lots of hours! A big thank you to you!

Roger Govier schrieb:

Hi

Well, in that case, use the sheet I sent you, but use the
following
formula in cell D2
=SUMPRODUCT(($B$2:$B$15=$F1)*
($B$2:$B$15<=$G1)*($A$2:$A$15))
Copy down through cells D3:E3
This will give the totals for the 3 calendar years beginning 01
Jan
2004.

Alternatively, just double click on the Filed in the Pivot Table
that
says Max of Amounts, and from the dropdown select Sum.

If you wanted the values for a range of 12 month periods beginning
with
the start dates in column B, and extending for a period of 1 year
from
that date, then enter the following in cell D2 and copy down to
cell
D15
=SUMPRODUCT(($B$2:$B$15=B2)*
($B$2:$B$15<=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))*
($A$2:$A$15))

I obtained values as shown below

191 291 286 273 244 221 216 176 163 139 140 1020 993 1007

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Many thanks - I received your email this time round.
Unfortunately
I
think there has been a misunderstanding, and re-reading my
original
post I think it was misunderstood (maybe I wasn't clear enough
or
just
wrongly described the problem). What the worksheet/formula does
is
find
*one* sum, the maximum/highest sum, within a 12 month period.
However
what I am looking for is a way to find the highest possible
total
sum
from any 12 month period - by adding all the individual sums
from
within that 12 month period.

That is my problem... apologies once again for maybe not making
this
quite clear enough.


Roger Govier schrieb:

Hi

Picked the email up this morning, and sent direct to your email
address
as provided.
Let me know if it doesn't arrive this time.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Unfortunately nothing arrived - I have emailed you directly
(excluding
'nospam' from your email). Thanks once again for your help.


Roger Govier wrote:
Copy workbook has been sent to your email address.

--
Regards

Roger Govier


wrote in message
ups.com...
Hi Roger,

Unfortunately, can't get it to work - the sum that is
achieved
is
way
lower than what I can calculate on my own by just summing
up
the
sums
from a 12 month period. I would be grateful if you could
send
me
a
copy
of those workbooks, my email is the same one I am posting
from,
I
will
be able to retrieve it from that account ).

Many thanks!

Roger Govier wrote:
Hi

It may be because you have headers, and when I tied it
first
I
omitted
the headers.
Assuming your data starts in row 2 then the array entered
formula
{=MAX((A2:A15)*(B2:B15=F1)*(B2:B15<=G1))}

I put my start date e.g. 01/01/04 in F1 and my end date
in
G1
e.g.
31/12/04 and this produced the correct result for me.

You do not need the double unary "--" after the "*" sign
as
in
my
first
formula (as Aladin was querying in his post).
This was a legacy from the fact that I was using a
formula
to
convert
your text values in column A from the cut and paste I
made
of
your
data.
I amended out the formula before posting, but omitted to
delete
the
double unary which is superfluous (even though it didn't
effect
the
result.)

You may not be getting it to work for a number of reasons
:-
You may have omitted to enter it as an array formula (see
notes
on
previous posting about using Control+Shift+Enter)
Your data in column A, may not be numeric, but text. I
assume
you
had
just typed the $ sign when posting, if your data has a
trailing
$
is
will not work.
Your dates may not be true Excel dates - well they
couldn't
be
if
what
you posted was from your data, as there is no date of 30
February
2005
(or any other year for that matter!!)

Try amending your data as suggested, and using the array
formula
as
above and see if that works.
Change the dates in F1 and G1 to any range you want, and
see
the
difference in the result., or put further sets of dates
in
G2:F2
etc
and
copy the formula down.

If you are still having difficulties, post back with your
real
email
address and I will send you a sample workbook with it
working.
You
could
also achieve what you want with a Pivot Table, again post
back
if
you
want me to describe that method.

--
Regards

Roger Govier


wrote in message
ups.com...
Roger,

Thanks for your reply. Unfortunately, the thing does
not
seem
to
work.
Could you explain in any more detail as to how the
formula
is
supposed
to work?

As said before, the starting sum is in A2 and the end
sum
is
in
A123.
The corresponding dates for each sum are from B2 to
B123.
Just
to
clarify, I would like to find out what the maximum sum
is
over a
12
month period - any 12 month period. The problem with
this
is
that
the
dates, as you can see in my original post, are not
incremental,
ie
1st
October, 2nd October, 3rd October and so forth.

What I can do manually of course is find out a 12 month
range
(say,
if
B5 is 4 October 2005 and B118 is 5 October 2006), and
then
sum
all
the
cells between A5 and A118. The problem with this is it
is
a
pain
to
go
through all possible variants.... ie 10 October 2005 to
9
October
2006,
then 11 October 2005 to 10 October 2006, etc..... (as
said
before,
the
date increments are random - so it is not always from a
specific
date
plus exactly one year).

any help would be greatly appreciated!


Roger Govier schrieb:

Hi

I put my start date in F1 and my end date in G1 then
the
array
entered
formula
{=MAX((A1:A14)*--(B1:B14=F1)*--(B1:B14<=G1))}
give the result.

To enter (or modify) an array formula, commit with
Control+Shift+Enter
and not just Enter
Do not type the curly braces { } yourself, if
you
use
Ctrl+Shift+Enter, Excel will insert them for you.

--
Regards

Roger Govier


wrote in message
ups.com...
hello,

was wondering whether anyone can give me any
pointers
as
to
how
to
identify the greatest possible sum from a column,
within a
12
month
period (in excel 2003, sp2).

basically, i have the following set up, in two
columns
(example):

amounts / date
13$ / 1 October 2004
5$ / 7 October 2004
13$ / 11 October 2004
29$ / 5 December 2004
23$ / 9 December 2004
5$ / 30 February 2005
53$ / 1 March 2005
13$ / 15 June 2005
24$ / 20 June 2005
13$ / 12 July 2005
113$ / 5 October 2005
13$ / 7 April 2006
993$ / 9 October 2006
14$ / 1 September 2006

what I seek is a formula that allows me to find the
greatest
possible
sum, but within a 12 month period. in the above
example, I
know
that
the sums from 1st of October 2004 until 1st of
October
2005
are
191$.
except I have to do this manually, and don't know
whether
for
instance
checking between 1 December 2004 and 1 December 2005
will
give
me a
bigger sum. is there any way to do this in excel?

anyone? :)

Thanks!