How can I find the greatest possible sum within 12 months? A newbie...

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Roger Govier]

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 Govier]

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.

 

[Aladin Akyurek]

What does

*--

mean?

 

[Aladin Akyurek]

What does

*--

mean?

 

[Guest]

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]

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.

 

[Roger Govier]

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.

 

[Guest]

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 ([email protected]).

Many thanks!

 

[Roger Govier]

Copy workbook has been sent to your email address.

 

[Guest]

Roger,

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

 

[Roger Govier]

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.

 

[Guest]

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]

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

 

[Guest]

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!

 

[Guest]

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]

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

 

[Roger Govier]

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