Forumla for averaging within a month
 

If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
€¦

Forumla for averaging within a month
 

Hi!

Try these:

Avaerage:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((ISNUMBER(A1:A10))*(MONTH(A1:A10)=1),B 1:B10))

Sum:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(MONTH(A1:A10)=1),B1:B10)

Change the month number (=1) as needed.

Biff

"Woody13" wrote in message
...
If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula
and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
.

Forumla for averaging within a month
 

On Thu, 28 Sep 2006 14:52:02 -0700, Woody13
wrote:

If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
…

To average between two dates:

A1: StartDate
A2: EndDate

Dates: Range of dates
Values: Range of values

=(SUMIF(DATES,"="&StartDate,VALUES) - SUMIF(DATES,""&EndDate)) /
(COUNTIF(DATES,"="&StartDate) - COUNTIF(DATES,""&EndDate))


--ron

Forumla for averaging within a month
 

Not sure what you mean by this: Entered as an array using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

Thanks


"Biff" wrote:

Hi!

Try these:

Avaerage:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((ISNUMBER(A1:A10))*(MONTH(A1:A10)=1),B 1:B10))

Sum:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(MONTH(A1:A10)=1),B1:B10)

Change the month number (=1) as needed.

Biff

"Woody13" wrote in message
...
If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula
and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
.

Forumla for averaging within a month
 

Type the formula, then, instead of hitting the ENTER key like you normally
would, you use the key combination of CTRL,SHIFT,ENTER.

Hold down both the CTRL key and the SHIFT key then hit ENTER. If done
properly Excel will enclose the formula in squiggly braces { }. You can not
just type these braces in, you MUST use the key combo.

Biff

"Woody13" wrote in message
...
Not sure what you mean by this: Entered as an array using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

Thanks


"Biff" wrote:

Hi!

Try these:

Avaerage:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((ISNUMBER(A1:A10))*(MONTH(A1:A10)=1),B 1:B10))

Sum:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(MONTH(A1:A10)=1),B1:B10)

Change the month number (=1) as needed.

Biff

"Woody13" wrote in message
...
If you have a string of dates in column A and numbers in column B, and
the
dates cover several years, is there a short cut way to write a formula
and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
.

Forumla for averaging within a month
 

Ron, it rejected my forumla, even for a simple sumif. Is there a cell that
should go before the = in =(SUMIF(DATES,"="&StartDate,VALUES)

"Ron Rosenfeld" wrote:

On Thu, 28 Sep 2006 14:52:02 -0700, Woody13
wrote:

If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
€¦

To average between two dates:

A1: StartDate
A2: EndDate

Dates: Range of dates
Values: Range of values

=(SUMIF(DATES,"="&StartDate,VALUES) - SUMIF(DATES,""&EndDate)) /
(COUNTIF(DATES,"="&StartDate) - COUNTIF(DATES,""&EndDate))


--ron

Forumla for averaging within a month
 

I realized I need to include =start date cell all in " ", but then the
answer came back zero. It seems as if SUMIF is meant to work when the
criteria is in the cells you are summing (VALUES) and not in the DATES. Is
there a way to toggle how the formula looks at criteria? Thanks

"Woody13" wrote:

Ron, it rejected my forumla, even for a simple sumif. Is there a cell that
should go before the = in =(SUMIF(DATES,"="&StartDate,VALUES)

"Ron Rosenfeld" wrote:

On Thu, 28 Sep 2006 14:52:02 -0700, Woody13
wrote:

If you have a string of dates in column A and numbers in column B, and the
dates cover several years, is there a short cut way to write a formula and
have it sum or average the information in a particular month? Thanks

Example
1/1/01 3.11
1/2/01 3.55
€¦

To average between two dates:

A1: StartDate
A2: EndDate

Dates: Range of dates
Values: Range of values

=(SUMIF(DATES,"="&StartDate,VALUES) - SUMIF(DATES,""&EndDate)) /
(COUNTIF(DATES,"="&StartDate) - COUNTIF(DATES,""&EndDate))


--ron

Forumla for averaging within a month
 

On Thu, 28 Sep 2006 15:49:02 -0700, Woody13
wrote:

I realized I need to include =start date cell all in " ", but then the
answer came back zero. It seems as if SUMIF is meant to work when the
criteria is in the cells you are summing (VALUES) and not in the DATES. Is
there a way to toggle how the formula looks at criteria? Thanks

No. That's not it.

The problem is a typo in what I had posted:


=(SUMIF(Dates,"="&StartDate,Values) - SUMIF(Dates,""&EndDate,Values))
/(COUNTIF(Dates,"="&StartDate) - COUNTIF(Dates,""&EndDate))


Here are the data I tested it on:

3/1/2006 StartDate
3/31/2006 EndDate


Dates Values
5-Jan 1
6-Jan 2
4-Mar 3
5-Mar 4
4-Sep 5
5-Jun 6


Result: 3.5

I used NAME'd ranges, but you can use direct references.
--ron

Forumla for averaging within a month
 

I'm still unable to get the basic SUMIF formula to work:
SUMIF(Dates,"="&StartDate,Values)

My exact formula reads like this: =SUMIF(C6:C429,"="H3,D6:D429) where H3
is the Start date. C column is dates, D is Value. It says there is an
error. It lets me do it if I change the criteria to this
=SUMIF(C6:C429,"=H3",D6:D429) but it just returns 0. Thanks for your
help.

"Ron Rosenfeld" wrote:

On Thu, 28 Sep 2006 15:49:02 -0700, Woody13
wrote:

I realized I need to include =start date cell all in " ", but then the
answer came back zero. It seems as if SUMIF is meant to work when the
criteria is in the cells you are summing (VALUES) and not in the DATES. Is
there a way to toggle how the formula looks at criteria? Thanks

No. That's not it.

The problem is a typo in what I had posted:


=(SUMIF(Dates,"="&StartDate,Values) - SUMIF(Dates,""&EndDate,Values))
/(COUNTIF(Dates,"="&StartDate) - COUNTIF(Dates,""&EndDate))


Here are the data I tested it on:

3/1/2006 StartDate
3/31/2006 EndDate


Dates Values
5-Jan 1
6-Jan 2
4-Mar 3
5-Mar 4
4-Sep 5
5-Jun 6


Result: 3.5

I used NAME'd ranges, but you can use direct references.
--ron

Forumla for averaging within a month
 

On Thu, 28 Sep 2006 17:14:01 -0700, Woody13
wrote:

I'm still unable to get the basic SUMIF formula to work:
SUMIF(Dates,"="&StartDate,Values)

My exact formula reads like this: =SUMIF(C6:C429,"="H3,D6:D429) where H3
is the Start date. C column is dates, D is Value. It says there is an
error. It lets me do it if I change the criteria to this
=SUMIF(C6:C429,"=H3",D6:D429) but it just returns 0. Thanks for your
help.

Woody,

You left out the ampersand (concatenation operator)

You have:

"="H3

it should be:

"=" & H3


--ron

Forumla for averaging within a month
 

THANKS!!!

"Ron Rosenfeld" wrote:

On Thu, 28 Sep 2006 17:14:01 -0700, Woody13
wrote:

I'm still unable to get the basic SUMIF formula to work:
SUMIF(Dates,"="&StartDate,Values)

My exact formula reads like this: =SUMIF(C6:C429,"="H3,D6:D429) where H3
is the Start date. C column is dates, D is Value. It says there is an
error. It lets me do it if I change the criteria to this
=SUMIF(C6:C429,"=H3",D6:D429) but it just returns 0. Thanks for your
help.

Woody,

You left out the ampersand (concatenation operator)

You have:

"="H3

it should be:

"=" & H3


--ron

Forumla for averaging within a month
 

On Thu, 28 Sep 2006 18:36:01 -0700, Woody13
wrote:

THANKS!!!


You're welcome. Glad you got it working.
--ron