Ignore errors in formula

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
918
Office Version
  1. 365
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
Hi
As my understanding
Try
Excel Formula:
G9=AGGREGATE(9,6,D9:F9)/C9
 
Upvote 0
Another option
Excel Formula:
=SUM(IFERROR(D9:F9,0))/C9
 
Upvote 0
Solution
Hi Fluff,mohadin and jtakw,

Thank you for the solution. It worked and appreciate your patience and valuable time. Have a great day ahead.?
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top