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.

What formula are you using?

Regards,
Fred.

"LD" wrote in message
...
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.

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)

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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.

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.

"Fred Smith" wrote:

What formula are you using?

Regards,
Fred.

"LD" wrote in message
...
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.

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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.

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))


--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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.

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

"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
Thanks heaps
This worked. I have been trying all day to get this right.

"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"LD" wrote in message
...
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.