Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Replace #NV with "0"

Copper Contributor

Hi Experts! I have a (hopefully) simple Problem with replacing #NV with "0" 

 

I have a simple Addition =C4+D9+K9 and one of these fields is a "#NV" result, so the response in this field is also "#NV".

 

1. Question: how can i solve this problem in the above mentioned field.

 

Otherwise:

 

2. Question: how can i solve the problem in the origin field, so the response will not be "#NV" , but "0"

 

=SVERWEIS(C25;$1:$1048576;4;FALSCH)

 

Sorry for my bad english.

 

 

 

    
4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Peter_Franz 

=WENNNV(SVERWEIS(C25;$1:$1048576;4;FALSCH);0)

 

You can wrap the formula in WENNNV.

@OliverScheurich 

Wow! Thank you very much for the really quick answer.

 

I did it but unfortunately i got a failure message: " too many arguments for this function" ..  Any idea?

@Peter_Franz 

wennnv.JPG

The formula returns the intended result. You can copy and paste the formula into your german Excel version and it will work. Perhaps you didn't copy the formula and manually entered too many arguments. 

Thanky our very much! I don`t know why but it works ! Great job! ;)

I`m thinking about the fact that my formula looks like the same, as the copy of your formula. The arguments seems to be the same...
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Peter_Franz 

=WENNNV(SVERWEIS(C25;$1:$1048576;4;FALSCH);0)

 

You can wrap the formula in WENNNV.

View solution in original post