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.

 

[Desu Nota from Columbus]

try using the =IF(ISERROR( function

http://www.techonthenet.com/excel/formulas/iserror.php

You can have it autofill blank cells with zeros.

 

[fromspenny]

Excel 2007

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	1	14	13		58		4	54	2	7	0
Sheet

 

[clupher]

Thanks for the prompt reply. However, the ISERROR route only returns zero in cell K1. I wondered if there is a way that the formula for returning the sum of largest 9 values would still work when 2 or more of the cells were blank. I would like for the computation to just assume one that if two cells or more are blank to just assume the value is zero for those blank cells but still want the formula to bring back the sum of the largest 9 (even if two or more of those cells turn out to have a value of zero). I was hoping to not have to use two different columns and am now curious if this can be done within one formula.

 

[shg]

Try =SUM(IFERROR(LARGE(A1:J1, {1,2,3,4,5,6,7,8,9}), 0))

... confirmed with Ctrl+Shift+Enter

 

[clupher]

How can I copy the row number and column letter into this thread? I would like to send an example of exactly what I am trying to do in excel?

 

[shg]

Follow the link to Excel Jeanie in post #3.

 

[clupher]

It is best if a describe an example. Here is what I am trying to achieve in a formula.
Assume the following:

cell A1=70
cell B1=80
cell C1=90
cell D1=100
cell E1=sum(large(a1:d1,{1,2,3}))

cell E should then give the answer 270. However, if I remove the data in cells A1 and B1 then I want cell E1 to give answer of 190. Assume the information in each cell is a test grade and I want E1 to provide me with the top 3 grades; however, some students might not take more than 1 test and the cells in 2 or more cells might be blank. I just want the blank cells to be assumed to provide a test grade of zero for those blank cells but still sum the the tests they did take.

 

[Matty]

Try:

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

Array entered...

Matty

 

[clupher]

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.