I want Cell to remain blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks to Frank Kabel, I have the formula below which works a treat. However, I would like Cell D11 to remain blank, rather than display #N/A, when no value appears in Cells B11 or C11.

Cell D11:

=INDEX(Rates!$B$2:$J$21,MATCH($C11,Rates!$A$2:$A$21,0),MATCH($B11,Rates!$B$2:$J$2,0))

Any ideas?
 
Hi
see your other thread. Hear the adaption for your cell ranges
=IF(COUNTA(B11:C11)=2,INDEX(Rates!$B$2:$J$21,MATCH($C11,Rates!$A$2:$A$2
1,0),MATCH($B11,Rates!$B$2:$J$2,0)),"")

P.S.: please stay in the original thread
 
Depends on what you mean by "blank".

- You can wrap in an IF statement to return "" insteadn of #N/A, as
Frank suggested. That will look blank, and will be ignored by functions
like COUNT(), AVERAGE(), etc. but will plot as zero and will cause
errors with +-*/ etc.

- You can use a conditional format based on the formula =ISNA(cell_addr)
to change the cell's font color to to white (or whatever your background
color is) when the formula returns #N/A. This will look blank, and will
receive no plot symbol, but will produce in a #N/A result in all
calculations, and will not cause graph lines to break like a blank cell
would.

- If you really and truly need a blank cell, nothing behaves exactly
like a blank cell excelpt a blank cell (i.e. delete the formula when it
returns #N/A). With dynamic data, you could write a chane event macro
that would delete or restore the formula as circumstances dictate.

Jerry

Lee said:
Thanks to Frank Kabel, I have the formula below which works a treat.
Click to expand...
 
Back
Top