# Ignore errors in formula

#### kumara_faith

##### Well-known Member
Hi,

I have the following table:

Book1
CDEFG
8PriceCapital 1Capital 2Capital 3Units
91010010010030
1010#NUM!100100#NUM!
Sheet1
Cell Formulas
RangeFormula
G9:G10G9=(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
CDEFG
8PriceCapital 1Capital 2Capital 3Units
91010010010030
1010#NUM!10010020
Sheet1
Cell Formulas
RangeFormula
G9G9=(F9+E9+D9)/C9

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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi,

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

Book3.xlsx
CDEFG
8PriceCapital 1Capital 2Capital 3Units
91010010010030
1010#NUM!10010020
Sheet972
Cell Formulas
RangeFormula
G9:G10G9=SUMIF(D9:F9,">0",D9:F9)/C9

Hi
As my understanding
Try
Excel Formula:
``G9=AGGREGATE(9,6,D9:F9)/C9``

Another option
Excel Formula:
``=SUM(IFERROR(D9:F9,0))/C9``

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

Glad we could help & thanks for the feedback.

Replies
4
Views
323
Replies
1
Views
267
Replies
1
Views
60
Replies
3
Views
262
Replies
5
Views
158

1,203,563
Messages
6,056,092
Members
444,846
Latest member
pbailey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back