software

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

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

gt; 2). I would also like to have a formula to do the Delta % from Current
gt; gt; Month amp; Average that populates with each month as the average does below.
gt; 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.

--
Bethquot;Bob Phillipsquot; wrote:

gt;
gt; quot;Bethquot; gt; wrote in message
gt; ...
gt;
gt; gt; 1) I am now wanting to compare the current Month vs. Average. I thought I
gt; gt; could use the same formula below, and then subtract the Avg. cell from the
gt; gt; current month cell, but that isn't seeming to work? Any ideas?
gt;
gt; Sounds okay. What formula did you use?
gt;
gt; gt; 2). I would also like to have a formula to do the Delta % from Current
gt; gt; Month amp; Average that changes with each month as the average does below.
gt; But
gt; gt; that is not working either.
gt;
gt; Again, show us the formula.
gt;
gt; gt; 3) In the formula that was giving to me below, I am not understanding the
gt; gt; concept of these two things-can someone explain them (It does however work
gt; gt; wonderfully!!)?
gt; gt;
gt; gt; Here is the entire formula:
gt; gt;
gt; =SUMPRODUCT(($R$11:$AC$11lt;=Q2)*$R$12:$AC$12)/SUMPRODUCT(--($R$11:$AC$11lt;=Q2)
gt; )
gt; gt;
gt; gt; Here are my questions:
gt; gt; Why multiply?
gt; gt; )*$R$12:$AC$12)/
gt; gt;
gt; gt; Why the dashes?
gt; gt; --($R$11:$AC$11lt;=Q2))
gt;
gt; They are used to coerce TRUE/FALSE results to 1/0 which SP can work on. I
gt; have no idea why your responder used both, they could have stuck to one. See
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
gt; explanation.
gt;
gt; BTW, I would the use the simpler, and more obvious
gt;
gt; =AVERAGE(IF($R$11:$AC$11lt;=Q2,$R$12:$AC$12))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt;
gt;
gt;
gt;
gt;

--
Beth