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.

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.

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 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? :)