Assuming Zero in Empty Cells

[clupher]

I have numbers in each of 10 cells horizontally across the excel page (A1 thru J1). In cell K1 I have the following formula =SUM(LARGE(A2:J2,{1,2,3,4,5,6,7,8,9})). Is there a way that if the numbers were missing for 2 or more cells in A1 thru J1 that it would just assign the blank cells a value of zero? If I remove the data/numbers from 2 or more cells in A1 thru J1 then my formula in K1 says #NUM!. Thanks so much.

 

[pgc01]

Hi
Welcome to the board

Try:

=SUM(IF({1,2,3}<=COUNT(A1:D1),LARGE(A1:D1,{1,2,3})))

 

[Matty]

I tried that formula and when I remove cell A1 and B1 cell E1 should have popped up with answer of 190 but instead it is just giving me the answer of 100.

Strange.

Works for me:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td style="text-align:right; ">90</td><td style="text-align:right; ">100</td><td>
</td><td style="text-align:right; ">190</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F1</td><td>{=SUM(IFERROR(LARGE(A1:D1,{1,2,3}),0))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Did you hit CTRL+SHIFT+ENTER when committing the formula? I reckon that's the problem.

Matty

 

[clupher]

Matty & pgc01--thanks so much. Both ways worked. Did not know about formula array (CTRL+SHIFT+ENTER) until now. It is so exciting to learn something new. Thank you both so much.

 

[clupher]

Just curious--why would this formula not work:

=(if({1,2,3}<=count(a2:d2),sum(a2:d2),large(a2:d2,(1,2,3))))

trying to understand. I would think that this statement above says If 1,2,3 is less than or equal to the count of A2:D2 then sum A2:D2. If not, then just take the largest numbers three numbers in A2:D2. IN other words, why does the sum have to come before the if in the statement?

 

[Matty]

Hi,

Are you familiar with the Evaluate Formula tool in Excel?

Have a play with it - it will help you to understand how these types of formulas are being evaluated.

Matty