Forumla for averaging within a month

W

Woody13

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
…
 
B

Biff

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),B1:B10))

Sum:

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

Change the month number (=1) as needed.

Biff
 
R

Ron Rosenfeld

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
…
Click to expand...

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
 
W

Woody13

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

Thanks
 
B

Biff

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
 
W

Woody13

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

Woody13

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
W

Woody13

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.
 
R

Ron Rosenfeld

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.
Click to expand...

Woody,

You left out the ampersand (concatenation operator)

You have:

">="H3

it should be:

">=" & H3


--ron
 
Top