# Why does this return #N/A?

I have this cell, H5, that returns #N/A when I think it should merely return a null. The cell it references is blank but does contain a formula. I've successfully been using IF(ref="".... to handle this up 'till now.

this is what's happening. what am I doing wrong here?

  in H5        =IF(L5="","",IF(B5="","",MATCH(L5,$A$1:A$30)+1)) #N/A in L5 =IF(B5="","",(INDIRECT("A"&ROW()))-1) blank in B5 03/02/2021  [Edit - Opaque] put code into preformatted textC:\fakepath\fortheweb.ods edit retag close merge delete ## Comments Shouldn't you use ; instead of , I don't know, if this us locale-dependend, but for me , means Excel and ; AOO and LO ( 2021-04-18 13:08:45 +0200 )edit @Wanderer - using , (comma) sadly is the default for US locale (for the sake of maximizing sources of error and confusion ;-(). You did not tell us, what's in A5: "A"&ROW() is A5 hence INDIRECT("A"&ROW()) evals A5 and in due to lack of that information there is nothing, so (INDIRECT("A"&ROW()))-1 yields -1 (L5) for me (and not blank, which I don't understand at all). And trying to MATCH(-1;...) in column A results (again due to lack of that information) to #N/A. PS: The whole thing is unclear to me. Why do you try to MATCH(L5,...) in $A$1:A$30 if L5 already contains, what you are trying to match. Hence you could use ROW()+1 as well (but may be I do miss something now)

( 2021-04-18 13:19:27 +0200 )edit

@Wanderer

Shouldn't you use ; instead of , I don't know, if this us locale-dependend

It is locale dependent. In locales that do not use , comma as decimal separator you can use that as function parameter separator (see Tools->Options->Calc->Formula) and is the default for those or at least some because that's what people are used to from English Excel, but ; semicolon (unlike Excel) works in all locales, which is why we use it here.

( 2021-04-18 14:29:37 +0200 )edit

@abrogard: please attach a sample file with actual data in A1:A30 where the formulas are supposed to work on. Please do not use the Answer field, edit your original question to provide further details. Thanks.

( 2021-04-18 14:34:00 +0200 )edit

thanks for the comment about ";" rather than "," - I didn't know which was better, they both seem to work. In A5 is a '1' What I'm meaning here is I'm specifying IF L5 = "" which it is. I'll go edit the question and provide a little spreadsheet. I did it. That should make things clearer. It's my own answer to my own question elsewhere.

( 2021-04-18 23:38:42 +0200 )edit

Sort by » oldest newest most voted

Hello,

you have deactivated Tools -> Options -> LibreOffice Calc -> View -> Section: Display -> Option: [ ] Zero values and hence you see a blank cell in L5, while it is in fact 0. There is no 0 in $A$1:A$30, hence there is no MATCH(0;$A$1:A$30) and you get #N/A.

PS: This sample proves the general rule, that it is bad idea to hide existing information from oneself.

more