PDA

View Full Version : Solved: Averages of column and formatting



Juicey27
09-08-2010, 01:19 PM
:dunno In column C i want to do two things:
(1)FORMAT COLUMN C > All numbers entered are formated in percentages. (no spaces after decimal) The formula that generates these percentages is in each cell of column C. It is . .
=SUM(F5:M5)/8

(2)Secondly, I want to obtain a running average of the entire column C

Problem I have now is that when I go to average all cells in column C, it averages in the 0% from having formatted the column to display percentage.

In cel N1 is where my formula is for averaging column C
=AVERAGE(C:C)

Please help . . thanks in advance

Bob Phillips
09-08-2010, 04:12 PM
Tell me again, what's the question(s)?

Juicey27
09-09-2010, 04:31 AM
When I go to compute my running average in cell N1. Since I have formatted column C to diplay number in a percentage format, any cell in column C that does not have data in it displays a 0% which is then incorporated into the running average in N1, screwing up this average. Please see screen attachment. See red arrow.
There is a userform attached to this and everytime i submit the form a new row of data is then dumped into this spreadh sheet. Column C has to be in a percentage format.

Is there a way to hide the formatting so it doesn't screw up the running average in cell N1?

Bob Phillips
09-09-2010, 04:36 AM
Try this array formula

=AVERAGE(IF(C1:C1000<>0,C1:C1000))

Juicey27
09-09-2010, 07:32 AM
This is not working. Please advise.

Bob Phillips
09-09-2010, 07:43 AM
I do not know how and why it is not working. Please advise.

Juicey27
09-09-2010, 08:14 AM
When I input the array formula in cell N1, it returns a 0% as the caculated average. I included a screen shot.

Aussiebear
09-09-2010, 12:09 PM
Try this array formula

=AVERAGE(IF(C1:C1000<>0,C1:C1000,""))

Bob Phillips
09-09-2010, 12:15 PM
When I input the array formula in cell N1, it returns a 0% as the caculated average. I included a screen shot.

You didn't array-enter it.

Juicey27
09-10-2010, 08:09 AM
This has been solved. Thank you "Distinguished Lord of VBAX" much appreciated.