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?

 

[paul]

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.

 

[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!

 

[Roger Govier]

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

 

[Guest]

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

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

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

 

[Roger Govier]

Hi

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

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

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

 

[Guest]

Thanks!