Averages
 

My first question has been answered below, however I now have 3 more
questions. Along the same lines (I believe):

1) I am now wanting to compare the current Month vs. Average. I thought I
could use the same formula below, and then subtract the Avg. cell from the
current month cell, but that isn't seeming to work? Any ideas?

2). I would also like to have a formula to do the Delta % from Current
Month & Average that changes with each month as the average does below. But
that is not working either.

3) In the formula that was giving to me below, I am not understanding the
concept of these two things-can someone explain them (It does however work
wonderfully!!)?

Here is the entire formula:
=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2))

Here are my questions:
Why multiply?
)*$R$12:$AC$12)/

Why the dashes?
--($R$11:$AC$11<=Q2))

Thanks a bunch. :)

************************************************** ****

Beth, I'd approach this differently.

In cells R11:AC11 enter dates such as 1/1/2006, 2/1/2006, 3/1/2006 and
format them any way you like. For instance, the could format as Jan,
Feb, Mar as long as the underlying number is the first day of each
month in the current year.

In cell Q2, enter the first day of the month through which the average
should be calculated. 2/1/2006 would mean calc through Februrary.

Then to calculate the average, use the following formula:

=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2))

The SUMPRODUCT to the left of the slash sums the values in row 12 for
all dates less than or equal to the date in Q2. The SUMPRODUCT to the
right of the slash counts the number of months that are less than or
equal to Q2.

Hope that helps.

- John

Hi,
I am creating a trend analysis spreadsheet. I have set up columns
Jan-Dec(Cells R12-AC12), Average(AD), Current Monthly amount vs. average(AF),
Delta (AG), YTD Actuals, YTD Budget, etc. etc.
Within this sheet I have created a formula in cell Q2 to make the data
within change per month. The formula is:
=CHOOSE($Q$2,R12,S12,T12,U12,V12,W12,X12,Y12,Z12,A A12,AB12,AC12). So when I
change the # in cell Q2, 1=Jan, 2=Feb, 3=Mar, etc. My Question is, I'm
trying to create averages based on the month I am in. If I am in February
(by hitting 2 in cell Q2), I want the avg. to calculate: Sum Jan + Feb /2 =
answer, if I am in May (by hitting 5 in Q2), I want it to calculate
Jan:May/5...I thought maybe Hlookup, but that isn't working for me? If I
manually type this in =Sum(R14:R15)/2=it stays the same when I change the
month within Q2-which means I would have to do a control F and replace

Averages
 

"Beth" wrote in message
...

1) I am now wanting to compare the current Month vs. Average. I thought I
could use the same formula below, and then subtract the Avg. cell from the
current month cell, but that isn't seeming to work? Any ideas?

Sounds okay. What formula did you use?

2). I would also like to have a formula to do the Delta % from Current
Month & Average that changes with each month as the average does below.
But
that is not working either.

Again, show us the formula.

3) In the formula that was giving to me below, I am not understanding the
concept of these two things-can someone explain them (It does however work
wonderfully!!)?

Here is the entire formula:

=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2)
)

Here are my questions:
Why multiply?
)*$R$12:$AC$12)/

Why the dashes?
--($R$11:$AC$11<=Q2))

They are used to coerce TRUE/FALSE results to 1/0 which SP can work on. I
have no idea why your responder used both, they could have stuck to one. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

BTW, I would the use the simpler, and more obvious

=AVERAGE(IF($R$11:$AC$11<=Q2,$R$12:$AC$12))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Averages
 

Bob,
What you have stated here doesn't work for me.
--
Beth


"Bob Phillips" wrote:


"Beth" wrote in message
...

1) I am now wanting to compare the current Month vs. Average. I thought I
could use the same formula below, and then subtract the Avg. cell from the
current month cell, but that isn't seeming to work? Any ideas?

Sounds okay. What formula did you use?

2). I would also like to have a formula to do the Delta % from Current
Month & Average that changes with each month as the average does below.
But
that is not working either.

Again, show us the formula.

3) In the formula that was giving to me below, I am not understanding the
concept of these two things-can someone explain them (It does however work
wonderfully!!)?

Here is the entire formula:

=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2)
)

Here are my questions:
Why multiply?
)*$R$12:$AC$12)/

Why the dashes?
--($R$11:$AC$11<=Q2))

They are used to coerce TRUE/FALSE results to 1/0 which SP can work on. I
have no idea why your responder used both, they could have stuck to one. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

BTW, I would the use the simpler, and more obvious

=AVERAGE(IF($R$11:$AC$11<=Q2,$R$12:$AC$12))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Averages
 

Bob,
WOOPS!! I missed the part about it being an Array formula-works like a
charm. Thanks. Thanks for the Link as well.

1) I am now wanting to compare the current Month vs. Average (Cell AD). I thought I
could use the same formula below, and then subtract the Avg. cell from the
current month cell, but that isn't seeming to work? Any ideas?

=AVERAGE(IF($R$11:$AC$11<=S2,$R$14:$AC$14))-AD14 (Not working)

2). I would also like to have a formula to do the Delta % from Current
Month & Average that populates with each month as the average does below.
But that is not working either. The original formula I was using is
=1-(AC14/AD14) but that would mean I have to manually change this as well.
Wasn't wanting to do that...

Thanks. :)

--
Beth


"Bob Phillips" wrote:


"Beth" wrote in message
...

1) I am now wanting to compare the current Month vs. Average. I thought I
could use the same formula below, and then subtract the Avg. cell from the
current month cell, but that isn't seeming to work? Any ideas?

Sounds okay. What formula did you use?

2). I would also like to have a formula to do the Delta % from Current
Month & Average that changes with each month as the average does below.
But
that is not working either.

Again, show us the formula.

3) In the formula that was giving to me below, I am not understanding the
concept of these two things-can someone explain them (It does however work
wonderfully!!)?

Here is the entire formula:

=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2)
)

Here are my questions:
Why multiply?
)*$R$12:$AC$12)/

Why the dashes?
--($R$11:$AC$11<=Q2))

They are used to coerce TRUE/FALSE results to 1/0 which SP can work on. I
have no idea why your responder used both, they could have stuck to one. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

BTW, I would the use the simpler, and more obvious

=AVERAGE(IF($R$11:$AC$11<=Q2,$R$12:$AC$12))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Averages
 

A tad more detail might be useful.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Beth" wrote in message
...
Bob,
What you have stated here doesn't work for me.
--
Beth


"Bob Phillips" wrote:


"Beth" wrote in message
...

1) I am now wanting to compare the current Month vs. Average. I
thought I
could use the same formula below, and then subtract the Avg. cell from
the
current month cell, but that isn't seeming to work? Any ideas?

Sounds okay. What formula did you use?

2). I would also like to have a formula to do the Delta % from
Current
Month & Average that changes with each month as the average does
below.
But
that is not working either.

Again, show us the formula.

3) In the formula that was giving to me below, I am not understanding
the
concept of these two things-can someone explain them (It does however
work
wonderfully!!)?

Here is the entire formula:


=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11<=Q2)
)

Here are my questions:
Why multiply?
)*$R$12:$AC$12)/

Why the dashes?
--($R$11:$AC$11<=Q2))

They are used to coerce TRUE/FALSE results to 1/0 which SP can work on.
I
have no idea why your responder used both, they could have stuck to one.
See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

BTW, I would the use the simpler, and more obvious

=AVERAGE(IF($R$11:$AC$11<=Q2,$R$12:$AC$12))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.