I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

Excel treats empty cells as 0.

So maybe you could use:
=SUM(LARGE(A1:D1,{1,2}))




Jagneel wrote:

I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

--

Dave Peterson

Dave

I tried this but if three of the cells are blank I get the #NUM! error.

I don't believe Excel is treating the blanks as 0 in this case.


Gord

On Wed, 13 Dec 2006 11:28:18 -0600, Dave Peterson
wrote:

Excel treats empty cells as 0.

So maybe you could use:
=SUM(LARGE(A1:D1,{1,2}))




Jagneel wrote:

I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

Oops. Bad testing on my part!

How about this array formula:

=SUM(LARGE(IF(A1:D1="",0,A1:D1),{1,2}))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Gord Dibben wrote:

Dave

I tried this but if three of the cells are blank I get the #NUM! error.

I don't believe Excel is treating the blanks as 0 in this case.

Gord

On Wed, 13 Dec 2006 11:28:18 -0600, Dave Peterson
wrote:

Excel treats empty cells as 0.

So maybe you could use:
=SUM(LARGE(A1:D1,{1,2}))




Jagneel wrote:

I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

--

Dave Peterson

Or

=SUM(LARGE(0+A1:D1,{1,2}))

still an array formula.

Gord Dibben wrote:

Dave

I tried this but if three of the cells are blank I get the #NUM! error.

I don't believe Excel is treating the blanks as 0 in this case.

Gord

On Wed, 13 Dec 2006 11:28:18 -0600, Dave Peterson
wrote:

Excel treats empty cells as 0.

So maybe you could use:
=SUM(LARGE(A1:D1,{1,2}))




Jagneel wrote:

I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

--

Dave Peterson

even better (not an array formula):

=MAX(A1:D1)+IF(COUNT(A1:D1)<2,0,LARGE(A1:D1,2))



Gord Dibben wrote:

Dave

I tried this but if three of the cells are blank I get the #NUM! error.

I don't believe Excel is treating the blanks as 0 in this case.

Gord

On Wed, 13 Dec 2006 11:28:18 -0600, Dave Peterson
wrote:

Excel treats empty cells as 0.

So maybe you could use:
=SUM(LARGE(A1:D1,{1,2}))




Jagneel wrote:

I would like empty cell to automatically set value 0.
For example. sum of largest two values in a row with
2, empty, empty, empty
I want the answer to be just 2 as it would if the data was
2,0,0,0

--

Dave Peterson