Ignore errors in formula

[kumara_faith]

Hi,

I have the following table:

Book1
	C	D	E	F	G
8	Price	Capital 1	Capital 2	Capital 3	Units
9	10	100	100	100	30
10	10	#NUM!	100	100	#NUM!
Sheet1

Cell Formulas
Range	Formula
G9:G10	G9	=(F9+E9+D9)/C9

I am trying to modify the formula to show the results by ignoring the error in the cells. Example, cell D10 has an error value. Therefore, the existing formula shoud ignore this error value and continue to calculate with the other value instead. The correct results for cell G10 are as follows:

Book1
	C	D	E	F	G
8	Price	Capital 1	Capital 2	Capital 3	Units
9	10	100	100	100	30
10	10	#NUM!	100	100	20
Sheet1

Cell Formulas
Range	Formula
G9	G9	=(F9+E9+D9)/C9

Is there a way to modify the formula to achieve this ? Appreciate all the help.

 

[jtakw]

Hi,

If you don't have negative values, you can get away with this:

Book3.xlsx
	C	D	E	F	G
8	Price	Capital 1	Capital 2	Capital 3	Units
9	10	100	100	100	30
10	10	#NUM!	100	100	20
Sheet972

Cell Formulas
Range	Formula
G9:G10	G9	=SUMIF(D9:F9,">0",D9:F9)/C9

 

[mohadin]

Hi
As my understanding
Try

G9=AGGREGATE(9,6,D9:F9)/C9

 

[Fluff]

Another option

=SUM(IFERROR(D9:F9,0))/C9

 

[kumara_faith]

Hi Fluff,mohadin and jtakw,

Thank you for the solution. It worked and appreciate your patience and valuable time. Have a great day ahead.?

 

[Fluff]

Glad we could help & thanks for the feedback.