Pleaes help me 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.







--
Beth
--
Beth

Not working means?
Did you array enter the array formula?

--
Don Guillett
SalesAid Software

"Beth" wrote in message
...
Pleaes help me 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.







--
Beth
--
Beth

Not working means I am unable to populate a formula that will change my
current month cell vs. average cell when changing from month to month without
manually having to enter in a formula for each month. I was hoping to have a
formula automatically populate information like I had done so with the
average cell (see beginning of this message). I did try to array the formula
but it is not working, I do not get the right number.

As for my 2nd question with Delta, same response above as to not working.
Does this make sense?
Thanks,
--
Beth


"Don Guillett" wrote:

Not working means?
Did you array enter the array formula?

--
Don Guillett
SalesAid Software

"Beth" wrote in message
...
Pleaes help me 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.







--
Beth
--
Beth

I believe I answered this in a different thread titled
Averages-HLOOKUP??.

- John