6

I am not programming in VBA. This is a simple excel spreadsheet. Essentially, I have a formula the finds the lowest price in a range of cells and returns its respective column title. It works fine. However, some ranges do not have any values at all. So, the cell that populates with the column header displays #N/A in this case.

What I need to do is check whether or not this happens. If the result is #N/A, I want the cell to show "No Values to Reference." I was thinking something along the lines of:

=IF(CELL != NULL, Display_Header, ELSE "No Bids to Reference")

OR

=IF(CELL = NULL, No Bids to Reference", ELSE Display_Header)

I have tried both NULL and #N/A but I am not sure how Excel handles these situations. That is, how Excel handles #N/A returns.

IF(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0))= "#N/A","No Bids To Reference", INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)))
sherrellbc
  • 4,222
  • 7
  • 37
  • 71
  • See: http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel and http://excel.tips.net/T002814_Returning_a_Blank_Value.html - this should be enough information for you to work backwards from for a solution – naththedeveloper Jun 12 '13 at 14:04
  • first thing would be to forget all the syntax you know about if clauses, cause Excel doesn't use that kind of syntax, but its own crappy syntax. Only my point of view but still... – Laurent S. Jun 12 '13 at 14:06

2 Answers2

8

Since you have only one calculation here, you can simply use an IFERROR:

=IFERROR(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)),"No Bids To Reference")

The IFERROR checks whether the expression evaluates to an error (it can be #N/A, #VALUE! or #REF!). If true, evaluate the next expression, otherwise return the first expression.

Jerry
  • 67,172
  • 12
  • 92
  • 128
  • 1
    @user2140261 Thank you for the +1. I'd up your answer if it were there ^^ – Jerry Jun 12 '13 at 14:15
  • Worked perfectly +1. I have another question if you are still around. Is there anything wrong with this formula? The result is zero if CELL =! 0 and it is less. – sherrellbc Jun 12 '13 at 14:36
  • 2
    @sherrellbc I'm not sure I understand you on that point... :s. The `IFERROR` checks whether the expression evaluates to an error (it can be `#N/A`, `#VALUE!` or `#REF!`). If true, evaluate the next expression, otherwise return the first expression. – Jerry Jun 12 '13 at 14:55
  • Sorry. I had posted a formula I was having trouble with just below my comment. I figured out what I was doing wrong and deleted the comment by forgot about the one above. All is well. – sherrellbc Jun 12 '13 at 15:16
  • @sherrellbc Oh, okay. That's fine :) – Jerry Jun 12 '13 at 15:26
2

I would CTL+G, Special, and replace the values of all cells with formula errors.

Andrew
  • 21
  • 1