# Excel 2007 Formula Using Multiple IF and ISERROR Statements

#### OkayKid

##### New Member
In E18, E19, and E20, I have formulas returning a result or an error, and in E17 I would like to populate the correct result, depending on whether or not the three cells are generating errors or not. Here's the kind of formula that I want to use but it's not working:

=(IF(ISERROR(E18),E19,E18),(IF((AND(ISERROR(E18), ISERROR(E19))),E20,),(IF(E18=0,"N/A",E18))))

At the moment, in E18 I have a #REF! error, in E19 there is a 0, and in E20 there is a 0. This formula is returning a #VALUE! error in E17, when I would like it to be showing the 0 in E19. And if E19 had a #REF! error, it would populate what E20 had in it. I hope this makes sense.

Any help would be much appreciated!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Andrew Poulsom

##### MrExcel MVP
That formula is syntactically incorrect, although it can be entered in a cell. Use the Formula Auditor to evaluate it and you will see what I mean.

#### OkayKid

##### New Member
Yeah it's definitely incorrect. I'm not sure what you mean by Formula Auditor. Is that Trace Error or Edit In Formula Bar options?

#### barry houdini

##### MrExcel MVP
If you just want the first non-error value in the range

=IFERROR(E18,IFERROR(E19,IFERROR(E20,""))

but you also have "N/A" in there, when should that apply?

#### OkayKid

##### New Member

The N/A should apply if E18=0.

#### barry houdini

##### MrExcel MVP
Well then you could try

=IFERROR(IF(E18=0,"N/A",E18),IFERROR(E19,IFERROR(E20,""))

#### T. Valko

##### Well-known Member

Yeah it's definitely incorrect. I'm not sure what you mean by Formula Auditor. Is that Trace Error or Edit In Formula Bar options?
The formula auditing tools are one of the best features in Excel (IMHO!).

In Excel 2007...
• Select the cell with the formula
• Goto the Formulas tab
• Formula Auditing>Evaluate Formula
I use this so often I put it on my QAT.

The icon also makes a nice avatar!

#### OkayKid

##### New Member
I've tried the formula auditor, and I feel like I'm starting to get somewhere with it, but I still can't get a working formula.

#### superstarprit

##### New Member
I've tried the formula auditor, and I feel like I'm starting to get somewhere with it, but I still can't get a working formula.

Can you share the excel file.... may i can help....

Replies
9
Views
42
Replies
20
Views
237
Replies
0
Views
29
Replies
5
Views
47
Replies
1
Views
59