Nested IF OR ISERROR

Question

I'm trying to write a formula and running into trouble. The problem is I need to write a nested IF that includes an OR and an ISERROR. I receive a spreadsheet from a supplier regarding stock available to me and I need to calculate the Unit Price (which changes daily because this is fruits and veggies).

Since I don't create this spreadsheet, I can't control how they supply the information. Worse yet, they tend to leave blank lines every once in a while, so I get Div/0 showing up, which the buyers hate. I just need to tell people if something is actually available AND the unit price if it is. Otherwise I need to leave the unit price column blank.

1  Column A           Column B             Column C                 Column D                           Column E

2  In Stock?            ITEM                    Case Price               Units Per Case                    Unit Price

3                             Carrots, 1#           $25.00                      50                                        $ 0.50

4     N/A                  Kumquats, 1#       $750.00                   30                                        $15.00

5                                                                                                                                      #DIV/0!

6                             Watermelon          $475.00                    35 or 40                             #N/A!

I would like rows 4, 5, and 6 all to show me a blank until I change row 6 to be one of those numbers. (Row 4 is not in stock, row 5 is blank, and row 6 has text in column D)

To me the logical formula in column E, row 3 (actually every row) would read =IF(OR($A3="N/A", ISERROR(C3/D3),""))

This is not acceptable, but I can't find anything that will tell me HOW to do this correctly.

Thank you in advance for any help you can give me on this.

Answer 1

Nancy wrote:

I'm trying to write a formula and running into trouble. The problem is I need to write a nested IF that includes an OR and an ISERROR. I receive a spreadsheet from a supplier regarding stock available to me and I need to calculate the Unit Price (which changes daily because this is fruits and veggies).

Since I don't create this spreadsheet, I can't control how they supply the information. Worse yet, they tend to leave blank lines every once in a while, so I get Div/0 showing up

[....]

To me the logical formula in column E, row 3 (actually every row) would read =IF(OR($A3="N/A", ISERROR(C3/D3),""))

This is not acceptable, but I can't find anything that will tell me HOW to do this correctly.

It is not uncommon to want to use ISERROR even though your are using Excel 2007.  Even though Excel 2007 has the IFERROR function, which is more efficient to use, ISERROR should be [1] used in compatibility mode; that is, in workbook that has an "xls" extension.  And that might very well be the case since the workbook comes from someone else, or if you want to make sure the workbook is usable by the broadest group of users.

So to answer your question, you might write:

=IF(OR(A3="N/A",ISERROR(C3/D3)),"",C3/D3)

---

[1] Technically, Excel 2007 and later permits us to use IFERROR in compatibility mode.  But we get a compatibility warning when we save the file (unless that option is disabled).  And the file is usable if it is opened in Excel 2003.

Answer 2

… To me the logical formula in column E, row 3 (actually every row) would read =IF(OR($A3="N/A", ISERROR(C3/D3),"")) …

It seems you should be using the new (Excel 2007 and newer) IFERROR(…) function in place of ISERROR(…). For you expressed sample row 3 try,

=IFERROR(IF(A3="N/A","",C3/D3),"")

… and fill down as necessary. This assumes that A4 is the text value N/A and not the Excel error value #N/A! like E6.

Edit: Since the IFERROR(…) wrapper will convert any error to its value_if_error (e.g. "" as above), it may make more sense to definitively throw an error in the case of column A being N/A.

=IFERROR(IF(A3="N/A",NA(),C3/D3),"")

IFERROR function
IF function
NA function

Answer 3

Thank you so much for this. You are absolutely correct that the workbook I receive is in an earlier version, so this is very helpful. 

I really do appreciate everyone's efforts here. There's always so much to learn, and it keeps my brain firing. Good stuff all.