I am doing scores for one of the leagues i shoot in. I am using excel.

I set up the whole thing and I got it working the way i want except for one thing. Believe me this took a while for me to do, when I was about done i found some easier ways to do things.

I have 8 weeks of scores 4 columns each first 2 are individual target score, 3rd is sum of the first 2 and 4th is an x count. At the end of the 32 columns i have 2 more columns 1st is a total points (Total of all 8, of the 3rd column) and a totla of the 8 weeks of X count. Then i have 2 more columns 1st is an avg of the 8, 3rd columns and the other is the avg of the 8 x count columns.

It all works except for the avg of the 8, 3rd columns. this is what the formula is in that cell. =AVERAGE(D2,H2,L2,P2,T2,X2,AB2,AF2)

The cell right next to it works for doing the same thing just a different set of cells. here is the formula in this cell
=AVERAGE(E2,I2,M2,Q2,U2,Y2,AC2,AG2)

What did I do wrong and how fo I fix it? I emailed it to someone but they were going out of town and didn't have time to take a look at it.

It doesn't seem like it should be this difficult.

thanks

 

I don't understand what the problem is.

You said the average of the X Count columns works too.

What message are you getting? Circular reference? Is it giving the same numbers? is it a ERR# message in the cell?

 

What is the error message?
Do you have the cell formatted already? Meaning, is another format taking over the calculation?

 

I am not getting an error message. i have the x count cell that give me the value i want. But the avg score cell is dividing the sum of the cells with numbers in them by 8 giving me the wrong result that i want in this cell.




25 25 50 2 50 2 6.2 2

Don't know how to represent it here, but if you look above the first 2 cells add up to the 3rd I have the 3rd cell formulated to do this. I think this is where my problem is. I have one total so far but the other 7 weeks have a 0 in the 3rd cell thus it adds all 8 cells and divides by 8. Since the 4th column is not formulated it only shows when i put something in it, thus now 1 has a number in it and 7 are blank not even a zero, so it only avgs the one cell with a number in it.

Is there a way to make the 3rd column not show a zero or to make the avg column not recognize a cell with the zero in it?

Man I wish i wasn't stupid.

 

So you're saying the column with the average isn't even showing a '7'????

 

Get this book. It's great.

Click Me.

 

I'm thinking you've got a bad formula in one of the other columns that the average is pulling data from. I just did a test with multiple columns and had zero's in some, and it averaged them all just fine.

 

The other cells seem to have a correct formula.

i just went and cleared the seven cells that i had formulated, which took the actual zero out of the cell, and it gives me the result i am looking for.

There has to be a way to do what I am doing so i can get the same result. I just can't figure it out.

If the cell is unformulated there is no value in it and my avg works out like i want, if i formulate the cell to give me a sum of the 2 cells to it's left, it puts a value of 0 in this cell and then adds this and divides it when it does the avg.

Meaning out of the 8 weeks, I have one week recorded now, with 7 unformulated cells, in that cell is 200, if i were to formulate say 4 more cells to give me a sum of the 2 cells to it's left and not put anything in them it would put a 0 in these and then my avg would not be 200 but would show as 40.

I am not sure how to explain it any better.

 

At first I thought it shoud be semicolons instead of commas.

I just made up somethng similiar, and did the forumla:
=average(a2,b2,c2,d2,f2,h2)

When I hit enter- boom, it averaged the scores.

You should not be getting an error, or characters in this cell, but, if you are try widening the cell first, if that doesn't claer it up, right click and go to format, cell and make sure it's set to numbers.

If all else fails... Just tell them you won, and call it a day!