On Jul 31, 7:47 am, Paige06 wrote:
I will copy one line and show it what I am trying to do.

Good. This is always good to do when you post here to request
assistance.


0 1,995 0 - - - - - - - - - 1,995 166

Well, I think we still have a communication problem. When I create a
column of constants that look that -- that is, 3 cells with 0, 1995
and 0 followed by 9 cells with a dash entered as text -- I have no
problem computing the 3-cell average with the expression
AVERAGE(A1:A12). It correctly computes 665 (1995/3), not 166.25
(1995/12) as your formula does.

So my question is: how are you creating the dashes?

That is, exactly what formula is in the data cells -- the cells
displaying 0, 1995, 0 and dashes? And what is the format of those
cells?

If they are simply references to another cell, I am asking for the
formulas in the target cells and the format of the cells that refer to
the target cells.

And what exactly is the formula that computed 166 above?

For future reference, it is always good to include this information in
your original posting. It will expedite your getting a correct
solution.


----- original posting -----


On Jul 31, 7:47*am, Paige06 wrote:
I tried both of the formulas you provided, it is still average all 12 months
I will copy one line and show it what I am trying to do. This sheet that i'm
averaging is also link from another sheet, so I don't think it will work the
way I want it to. But hey this is a good learning experience.
Educational Sales * * * 0 * * * 1,995 * 0 * * * *- * * * - * * * - * * * - * * * - * * * - * * * - * * * - * * *
- * * * 1,995 * 166
the o and figure and dashes represent a month. the dashes are being counted,
in the average. the o's i deleted the link and added them. I'm thinking this
is not going to work.



"joeu2004" wrote:
On Jul 30, 11:45 am, Paige06
wrote:
Ok, I just figured out why I'm getting an incorrect average using example:
average(B38:M38), because it's taking all 12 months and averaging. I do not
want that. I want it to average as I enter numbers or not in month to month.
Understand?

Forgive me if I'm being anal, but I still do not understand. *Here is
what I understand the problem to be....

You are averaging all 12 months, but only the first "n" months have
valid data. *You want to average only the first "n" months, which
might include zero-valued sales.

Well, a simple AVERAGE(B38:M38) would do just that, but __only_if__
either the cells after the first "n" cells are empty (no formula and
no constant value), or they
contain a formula that evaluates to the null string.

I suspect you are entering zero into those cells. *It would be better
to leave them empty.

The problem with any formula that tries to exclude zero-valued cells
from the average is: *if sales are actually zero in one of the first
"n" months, you will inadvertently exclude those cells from the
average. *My understanding is: *you don't want to exclude those
"valid" zero values.

If you "must" have zero represent both zero sales and "no data", you
need to test some other condition to distinguish the two. *For
example, use the following array formula (commit with ctrl-shift-
Enter, not just Enter; if necessary, re-enter by pressing F2 followed
by ctrl-shift-Enter):

=AVERAGE(IF(MONTH(B37:M37)<MONTH(TODAY()), B38:M38))

Alternatively, use the following non-array formula:

=SUM(B38:M38) / SUMPRODUCT( --(MONTH(B37:M37) < MONTH(TODAY()) ) )

The "--" is a trick to make the boolean expression be treated as the
number 1 or 0.

Doesn't that help?

----- original posting -----

On Jul 30, 11:30 am, Paige06
wrote:
Thanks Joe,

I will try to explain again more clearly. I was a little frazzeled when I
wrote that.
I have 1 main sheet that I enter number into each month. From that sheet I
have each cell for each sales guy linked into the 2nd sheet. On the second
sheet for each month I want an average of sales numbers calculating as I
enter numbers each month. I wasn't aware that the orginal formula I had
excluded 0. I have tried to change the formula and it still will not
calculate each month even with the 0's, only the month with the number
amount. And if I try to use =average A1:A15 it gives a wrong number.. But if I
open a new sheet it works the way I want it to. ???????? I'm not by any means
an excel expert, but I do know a little. And yes, my formula was an array..

"joeu2004" wrote:
On Jul 30, 9:19 am, Paige06 wrote:
I need to average a row *some cells have 0's but I need to include them in
the function so it averages correctly. The formula I currently have is this
=AVERAGE(IF(B38:M38<0,B38:M38,"")). It is working with the cells with
numbers, but not the cells that do not. Example : jan - 5, feb - 7, mar. 0,
april - 18 = 30 divided by 4 = 7.50. My sheet will only calulate 3. Any
suggestions?????

You intentions are unclear to me.

First, your formula says that you want to exclude zero-valued cells
from the average. *But your first statement says "I need to include
them", and your example ("divided by 4") and your intended result
(7.5) suggests that you do indeed want to include zero-valued cells..

Second, from the presentation of the example, it is unclear to me
whether the range B38:M38 includes just cells with numeric values
(e.g. 5, 7, 0 and 18 in the example) or also cells with text (e.g.
literally "jan", 5, "feb", 7, "mar", 0, "april", 18). *That may or may
not have anything to do with your problem.

Finally, you provide nothing in your example that would explain how
your formula returns 3. *I suspect the value of 3 is coming from
different example, not the example supplied in your posting.

Having said that, looking only at the syntax of the example formula
above, did you remember to enter it as an array formula (commit with
ctrl-shift-Enter, not just Enter)?

If you do not, then the formula above is logically equivalent to:

=if(B38<0, average(B38:M38), 0)

That may or not may not be the root cause of your problem, given that
I do not understand your intentions.- Hide quoted text -

- Show quoted text -