'Hide #NA displayed in the cell by formula =IFERROR(~~~,NA())

I'm trying to format my excel sheet and to display organized chart, I have to do( A1~A100 )

IFERROR($B1+$C1,NA())

To make cells #NA value instead of

IFERROR($B1+$C1,"")
IFERROR($B1+$C1,"-")

because those "" or "-" is going to be shown as 0 value on chart and it looks really bad on sight.

But I don't want #NA to be shown on my cells. It's clearly makes my sheet looks messy.

I tried to do Conditional Formatting to do 'New Rule...'→'Use a formula to determine which cells to format'→=ISNA(A1)→white blank It worked but since the rule only applies only when "A1" is not #NA and not to others, I don't think this is a practical solution.

Solution 1:^[1]

Try this in your formatting =ISNA($A1) then drag down copy the cell after clicking apply.

Solution 2:^[2]

I don't see any other choice than using range1 (your current range) for calc and as chart source, then create range2 pointing to range1 using IFERROR, then finally hiding range1.
A bit clumsy, but that's the only way I see here to separate data from presentation.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Nir14
Solution 2	iDevlop

'Hide #NA displayed in the cell by formula =IFERROR(~~~,NA())

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]