Replacing an empty cell

[LD]

How can I replace an empty cell with a zero value or get a formula to
recognise a blank cell as a zero value in a claculation? For example I have 6
marks for each of my students. I need to be able to sum their best 5 marks. I
can do this for those students who have completed all 6 tasks but for those
with marks missing the formula wont work. I am using office 2003.

 

[Fred Smith]

What formula are you using?

Regards,
Fred.

 

[T. Valko]

How can I replace an empty cell with a zero value

I need to be able to sum their best 5 marks.

Try one of these:

This is an array formula** :

=SUM(LARGE(A1:F1+0,{1,2,3,4,5}))

Or, this normally entered version:

=SUM(LARGE(INDEX(A1:F1+0,0),{1,2,3,4,5}))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[LD]

The formula I am using is
=(SUM(A1:F1)-LARGE(A1:F1,6)). This works fine when there are six entries but
for a row with less than 6 I get #VALUE! in the cell. There are too many
entries to change each one by hand.

 

[LD]

Thanks
I tried the normally entered version but still get #VALUE! in the cell when
there are less than 6 entries. The formula I have been using is
=(SUM(A1:F1)-LARGE(A1:F1,6))

 

[T. Valko]

I tried the normally entered version but still get #VALUE!

Are there any TEXT entries in the range?

Try this:

=SUM(A1:F1)-IF(COUNT(A1:F1)=6,MIN(A1:F1))

 

[LD]

Thanks heaps
This worked. I have been trying all day to get this right.

 

[T. Valko]

You're welcome. Thanks for the feedback!