Calculating an Average but ignore cells with no data yet

[DentonHTHS]

Hi,
I am working a sheet to average students weekly marks but would like to be able to give them their current mark and have excel ignore the assignment cells that don't have marks yet. This is what I have so far. I think it's an IF statement but not sure how to work it out completely.

Here's the formula I have so far.

=AVERAGE(D4,L4,T4,AB4,AJ4,AR4,AZ4,BH4,BP4,BX4,CF4,CN4,CV4,DD4,DL4,DT4,EB4,EJ4)

Thanks for any help.

 

[Jonmo1]

Is there any other method to determine which cells to average?
Like a certain text string in a header row, so we could say
Average row 4 if the text in row 1 equals "assignment"
Or something like that?

 

[DentonHTHS]

Each week has a number of assignments which are then averaged on a "Mark for the week" column. The cells identified above are the ones that are those weekly columns. I'm not sure how to post the gradebook sheet here so that's why I just posted the formula. Does that help?

 

[Jonmo1]

Try this assuming you have XL2007+
Also assuming row 1 has the text "Mark for the week" in the columns you want averaged.

=AVERAGEIFS($D4:$EJ4,$D4:$EJ4,"<>0",$D$1:$DJ$1,"Mark for the week")

 

[Joe4]

I am working a sheet to average students weekly marks but would like to be able to give them their current mark and have excel ignore the assignment cells that don't have marks yet

Are the cells that don't have marks blank/empty?

If so, the AVERAGE function should already ignore blank entries, so you shouldn't have to do anything special to exclude them.

 

[DentonHTHS]

Okay, so I have student who has two weeks of marks 100 and 72. This should average out to 86 but the formula is returning 9.56. I thought it was because it was counting all the empty cells as "0" but now I'm not sure what is happening.

 

[DentonHTHS]

Try this assuming you have XL2007+
Also assuming row 1 has the text "Mark for the week" in the columns you want averaged.

=AVERAGEIFS($D4:$EJ4,$D4:$EJ4,"<>0",$D$1:$DJ$1,"Mark for the week")

The titles were actual in the 2nd row and the name was slightly different than I quoted so I fixed those in your formula to match it to the sheet. However it is returning a #Value! error

=AVERAGEIFS($D4:$EJ4,$D4:$EJ4,"<>0",$D$2:$DJ$2,"Grade for week")

 

[Joe4]

Did you see my reply in post #5?
What exactly is in these cells with no grades?

 

[DentonHTHS]

Did you see my reply in post #5?
What exactly is in these cells with no grades?

Right now they have the formula to sum up the work for the week (=SUM(CE4,CB4)) and the cell has 0.00 in it for each "Grade for Week" until marks are added.

 

[Joe4]

Right now they have the formula to sum up the work for the week (=SUM(CE4,CB4)) and the cell has 0.00 in it for each "Grade for Week" until marks are added.

OK. Then you will probably want to go down the road that Jon is suggesting.