averaging numbers

[Jeff]

Using Excel 2007
I need to average numbers. The numbers are in the same rows _but_ not in
consecutive columns. The problem I am trying to get around is that in
some rows, some cells are blank (do not contain a number) and I
therefore do not want them included in the calculations even though the
formula includes them.

How does "=AVERAGE" handle blank cells? Do I have to do anything special
to make XL ignore the blank cells when averaging the numbers?

Thank you.

Jeff

 

[Pete_UK]

AVERAGE ignores blank cells. Suppose you have 2 in A1, A2 is empty and
A3 contains 4, then:

= AVERAGE(A1:A3)

will return 3, not 2, showing that A2 has not figured in the
calculation.

Hope this helps.

Pete

 

[Jeff]

AVERAGE ignores blank cells. Suppose you have 2 in A1, A2 is empty and
A3 contains 4, then:

= AVERAGE(A1:A3)

will return 3, not 2, showing that A2 has not figured in the
calculation.

Hope this helps.

Pete

Thank you that is what I needed to know.

Now here I'm getting fancy. I do not even know if this is possible but
it would be nice if it were:

The formula
=AVERAGE(U4,Z4,AE4,AJ4,AO4,AT4,AY4)
yields the correct average I need.

OK. Is there a way to have that result column show, not just the
average, but the values of U4, Z4, etc followed by some separator and
then the average from that formula?

This would let me see at a glance if there were any obvious outliers.

Jeff

 

[Pete_UK]

You could use a formula like this:

=U4&","&Z4&","&AE4&","&AJ4&","&AO4&","&AT4&","&AY4&"…"&AVERAGE(U4,Z4,AE4,AJ4,AO4,AT4,AY4)

which will separate each value with a comma and the average is
separated by ...

If you have fractional values then you might want to do something like
this:

=TEXT(U4,"0.00")&","&TEXT(Z4,"0.00")&","& ...

and so on, so that each value is shown to 2 deciamal places.

Hope this helps.

Pete

 

[Jeff]

You are terrific. Thank you very much

Jeff

You could use a formula like this:

=U4&","&Z4&","&AE4&","&AJ4&","&AO4&","&AT4&","&AY4&"…"&AVERAGE(U4,Z4,AE4,AJ4,AO4,AT4,AY4)

which will separate each value with a comma and the average is
separated by ...

If you have fractional values then you might want to do something like
this:

=TEXT(U4,"0.00")&","&TEXT(Z4,"0.00")&","& ...

and so on, so that each value is shown to 2 deciamal places.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Jeff - thanks for feeding back.

Pete