Excel 2007 Formula Using Multiple IF and ISERROR Statements

[OkayKid]

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!

 

[Andrew Poulsom]

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]

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]

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]

The N/A should apply if E18=0.

 

[barry houdini]

Well then you could try

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

 

[T. Valko]

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]

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]

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....