Circular reference error

Question

I am trying to create a formula to calculate the average of a column on numbers. After the average is calculated, I want to apply the average less 1 to any blanks in the original column of numbers

For example, I have 32 member in a group and each one that attends gets some performance points. I take an average of the members that attended and would like to assign the absent members the average points of the members that attended less a penalty for not attending.

I run into a circular reference problem as soon as I try to use a formula to assign points to the absent member.

For example with 32 members with 2 absent, I calculate the average of the 30 members present. (all is good so far). but as soon as I try to create a formula to assign an amount for the absent member I get a circular error message because the absent members points are added into the column to calculate the original average.

Thanks in advance for any help you can provide.

Answer 1

> ..., I want to apply the average less 1 to any blanks

Hi.  I'm going to guess at another approach.  I'd like to use your example.  Here's my guess of course...

The average of course is the total / 9  or 46/9 = 5.111 as given

If each missing student is to have a score 1 less then the average, then here's an idea.

The penalty has to be applied to everyone.   (I used penalty of 1)

Missing student average = 

(46 -12*1) / 9  = 3.777

If you place 3.777 in the missing values( instead of 5.111), the overall average is 4.777.

These students have 1 less than the average.

Of course, I may of misunderstood the question. 

= = = = =

HTH  :>)

Dana DeLouis

Answer 2

In C we have either a number or a blank

In D we have a formula

This could be modified to =IF(C1,C1,Round(AVERAGE($C$1:$C$12),0))

Copy D1 down the column

If you wish copy all of D and then use Paste Special Values to put numbers in C; and now delete D

best wishes

Answer 3

As soon as I try to assign a value to the absent member, I get a circular reference error.

What you are describing is the precise definition of a circular reference. If you reference a range of cells with a function like AVERAGE() that may include blanks, filling those blanks in after-the-fact with something that references the AVERAGE formula is circular.

Answer 4

This did not work for my specific issue -- I may not have described the issue well enough.

Column A - list of 32 names -- these names and the order do not change

Columns C:T performance scores (18 weeks)

Week 1 (Column C) points are earned for each of the 32 members that participated

If a member is not there the points are blank

I would like the calculate the average of the members that participated and based on that value, I would like to be able to give that value to the absent member.

As soon as I try to assign a value to the absent member, I get a circular reference error.

Answer 5

With the member names in A2:A31 and their respective points in B2:B31 try this deconstruction of the AVERAGE() function,

=SUM(B2:B31,COUNTBLANK(B2:B31)*(AVERAGE(B2:B31)-1))/COUNTA(A2:A31)

Edit: got rid of a few brackets by collecting the sums into SUM().