Averages-HLOOKUP??

[Guest]

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,AA12,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
everytime I want a different month as opposed to having a formula.
Does this make sense?
Thanks so much in advance!

 

[Guest]

The last line I didn't say right, I want a formula that calculates for each
month when changing cell Q2, as opposed to having to manually changing within
each month.

 

[John Michl]

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

 

[Guest]

IT WORKS!!!! GOD BLESS YOU!!!
Thanks John!!

 

[Guest]

John,
Now that I know this works, can you break this part of it down for me? I'm
not "getting' the concept:

Why multiply?
)*$R$14:$AC$14)/

What are the --'s for?
(--($R$11:$AC$11<=Q2))

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

 

[John Michl]

I'll give a quick review of this particular formula but to really
understand the in's and out's of this powerful function see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html.

First break it in two parts:
SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$14:$AC$14)
.....($R$11:$AC$11<=Q2)...creates an array of TRUE and FALSE values for
each cell in the range of R11:AC11 as that cell is compared to Q2.
....$R$14:$AC$14...creates an array of values from row 14
when multipling these two arrays the TRUE/FALSE change to 1 and 0.
SUMPRODUCT multiplies the corresponding values from the arrays then
adds the result together. For purpose of example, replace the dates in
row 11 with 1 for the Jan, Feb and Mar columns and zero for the rest
meaning that Q2 = March 1. The sumproduct formula would be (1*R14 +
1*S14 + 1*T14 + 0*U14...) which in effect is the sum of the all values
in Row 14 that meet the criteria.

SUMPRODUCT(--($R$11:$AC$11<=Q2­))
....This formula is basically a count of months that meet the
criteria...
Think of the -- as an operand that converts the TRUE and FALSE values
to 1 and 0. This happened automatically in the first half because of
the multiplication function. A better explanation is found in the link
mentioned at the start.

- John

 

[Guest]

Thanks for the reply, you have been the best help yet.

Could you Please help me to compare the current Month vs. Average (Cell AD).
I thought I
=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
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.

 

[John Michl]

There are a number of issues with your formula. You can't use and
array in an IF statement (R11:AC11) unless you enter it as an array
formula or use it with a function that accepts an array such as SUM,
COUNT, SUMPRODUCT, etc. In any event, I'd start with the average
formula we did before and the replace the AD14 in your example with
another sumproduct that retrieves the value equal to the current month.
Typically I'd use VLOOKUP in this case but since we already have the
SUMPRODUCT worked, use that for consistency.

Formula for average:
=SUMPRODUCT(($R$11:$AC$11<=Q2)*$R$14:$AC$14)/SUMPRODUCT(--($R$11:$AC$11<=Q2­))

Formula for current month: =SUMPRODUCT(($R$11:$AC$11=Q2)*$R$14:$AC$14)
(note the =Q2 instead of <=Q2)

Combine both formulas with a minus sign in between and you'll have your
formula for #1 for AF. Since this will always use the current month
information, your formula for #2 can just reference the previously
calculated cells. No changes to that cell would be necessary each
month.

- John Michl