calculating

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tofimoon4 - 12 Jun 2007 15:47 GMT

My dear, please tell me ( with example) how to calculate values between
two dates .

Signature

tofimoon4

Reply to this Message

Sandy Mann - 12 Jun 2007 18:43 GMT

Speaking for myself, I am not sure what it is you are asking. Why don't YOU
give US an example for what you want.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> My dear, please tell me ( with example) how to calculate values between
> two dates .

Reply to this Message

tofimoon4 - 13 Jun 2007 07:48 GMT

Sandy Mann;508282 Wrote:

> Speaking for myself, I am not sure what it is you are asking. Why don'
> YOU
[quoted text clipped - 3 lines]
> between
> > two dates .

my dear, i mean if i have invoices with several dates like below :
1/5/2007 700
1/12/2007 1250
1/26/2007 750
2/6/2007 2000
2/15/2007 1850
2/25/2007 3200
3/1/2007 7500
and i need to calculate these values every 15 days or monthly

--
tofimoon4

Reply to this Message

Sandy Mann - 13 Jun 2007 15:11 GMT

Monthly Totals:

January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))

February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))

March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))

You don't say where the 15 days start so assuming that they start on 1/1/07:

1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)

Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)

Etc.

That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Sandy Mann;508282 Wrote:
>> Speaking for myself, I am not sure what it is you are asking. Why don't
[quoted text clipped - 14 lines]
> 3/1/2007 7500
> and i need to calculate these values every 15 days or monthly.

Reply to this Message

Sandy Mann - 13 Jun 2007 15:40 GMT

I forgot to change the dates in the 15 day formulas from my British style
into American style.

It would be better to place the date in cells - say 1/1/07 in C1, 1/15/07 in
C2, 1/30/07 in C3 etc. then the formulas would be:

1st 15 days:

=SUMPRODUCT((A1:A7>=C1)*(A1:A7<=C2)*B1:B7)

2nd 15 days:
=SUMPRODUCT((A1:A7>C2)*(A1:A7<=C3)*B1:B7)

No need for the confusing double unary.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Monthly Totals:
>
[quoted text clipped - 39 lines]
>> 3/1/2007 7500
>> and i need to calculate these values every 15 days or monthly.

Reply to this Message

Rate this thread: