Excel 2007 Formula Using Multiple IF and ISERROR Statements

OkayKid

New Member
Joined
Jan 12, 2011
Messages
15
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!
 

Some videos you may like

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jan 12, 2011
Messages
15
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jan 12, 2011
Messages
15

ADVERTISEMENT

The N/A should apply if E18=0.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Well then you could try

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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
Joined
Jan 12, 2011
Messages
15
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,651
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top