> 1) I am 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" <[email protected]> wrote in message
> news:[email protected]...
>
> > 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.
>
>
>
>
>
>

--
Beth