|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
paul.shepherd[ at ]nospamparadise.net.nz
remove nospam for email addy!
"spam[ at ]redo.net" wrote:
[Quoted Text] > 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
<spam[ at ]redo.net> wrote in message
news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.com...
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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
>
>
> <spam[ at ]redo.net> wrote in message
> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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
<spam[ at ]redo.net> wrote in message
news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.com...
[Quoted Text] > 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
>>
>>
>> <spam[ at ]redo.net> wrote in message
>> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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" <akyurek[ at ]xs4all.nl> wrote in message
news:450459c6$0$4514$e4fe514c[ at ]news.xs4all.nl...
[Quoted Text] > 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 (spam[ at ]redo.net).
Many thanks!
Roger Govier wrote:
[Quoted Text] > 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
>
>
> <spam[ at ]redo.net> wrote in message
> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
> >>
> >>
> >> <spam[ at ]redo.net> wrote in message
> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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
<spam[ at ]redo.net> wrote in message
news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.com...
[Quoted Text] > 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 (spam[ at ]redo.net).
>
> 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
>>
>>
>> <spam[ at ]redo.net> wrote in message
>> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
>> >>
>> >>
>> >> <spam[ at ]redo.net> wrote in message
>> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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:
[Quoted Text] > Copy workbook has been sent to your email address.
>
> --
> Regards
>
> Roger Govier
>
>
> <spam[ at ]redo.net> wrote in message
> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
> >
> > 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
> >>
> >>
> >> <spam[ at ]redo.net> wrote in message
> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
> >> >>
> >> >>
> >> >> <spam[ at ]redo.net> wrote in message
> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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
<spam[ at ]redo.net> wrote in message
news:1158196679.905046.89980[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text] > 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
>>
>>
>> <spam[ at ]redo.net> wrote in message
>> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
>> >
>> > 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
>> >>
>> >>
>> >> <spam[ at ]redo.net> wrote in message
>> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
>> >> >>
>> >> >>
>> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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:
[Quoted Text] > 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
>
>
> <spam[ at ]redo.net> wrote in message
> news:1158196679.905046.89980[ at ]b28g2000cwb.googlegroups.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
> >>
> >>
> >> <spam[ at ]redo.net> wrote in message
> >> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
> >> >
> >> > 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
> >> >>
> >> >>
> >> >> <spam[ at ]redo.net> wrote in message
> >> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
> >> >> >>
> >> >> >>
> >> >> >> <spam[ at ]redo.net> wrote in message
> >> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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
<spam[ at ]redo.net> wrote in message
news:1158294502.652840.288580[ at ]i3g2000cwc.googlegroups.com...
[Quoted Text] > 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
>>
>>
>> <spam[ at ]redo.net> wrote in message
>> news:1158196679.905046.89980[ at ]b28g2000cwb.googlegroups.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
>> >>
>> >>
>> >> <spam[ at ]redo.net> wrote in message
>> >> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
>> >> >
>> >> > 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
>> >> >>
>> >> >>
>> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
>> >> >> >>
>> >> >> >>
>> >> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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:
[Quoted Text] > 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
>
>
> <spam[ at ]redo.net> wrote in message
> news:1158294502.652840.288580[ at ]i3g2000cwc.googlegroups.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
> >>
> >>
> >> <spam[ at ]redo.net> wrote in message
> >> news:1158196679.905046.89980[ at ]b28g2000cwb.googlegroups.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
> >> >>
> >> >>
> >> >> <spam[ at ]redo.net> wrote in message
> >> >> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
> >> >> >
> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >> <spam[ at ]redo.net> wrote in message
> >> >> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> <spam[ at ]redo.net> wrote in message
> >> >> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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
<spam[ at ]redo.net> wrote in message
news:1158454159.810120.235710[ at ]d34g2000cwd.googlegroups.com...
[Quoted Text] > 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
>>
>>
>> <spam[ at ]redo.net> wrote in message
>> news:1158294502.652840.288580[ at ]i3g2000cwc.googlegroups.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
>> >>
>> >>
>> >> <spam[ at ]redo.net> wrote in message
>> >> news:1158196679.905046.89980[ at ]b28g2000cwb.googlegroups.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
>> >> >>
>> >> >>
>> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> news:1158150916.333739.75490[ at ]i42g2000cwa.googlegroups.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 (spam[ at ]redo.net).
>> >> >> >
>> >> >> > 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
>> >> >> >>
>> >> >> >>
>> >> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> >> news:1158076111.588964.90490[ at ]i42g2000cwa.googlegroups.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
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> <spam[ at ]redo.net> wrote in message
>> >> >> >> >> news:1157713865.375726.159600[ at ]i3g2000cwc.googlegroups.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 | | |