Change #REF-Error into #NV-Error for array-formula

[hglamy]

Hello there,

I use a number of array-formulas to bring about the data for a chart.

Where there are no values, I would need #NV-errors, so that these
cells are omitted in the chart.

The array-formula produces #BEZUG-errors, however (German version).

Can I transform the #BEZUG-errors into #NV-errors within an array-formula,
and if so, how ?

Help is greatly appreciated.

Kind regards,

H.G. Lamy

 

[Peo Sjoblom]

Something like

=IF(ISTFEHL(your formula);NV();your formula)

could of course be a problem if your formulas are long

ISTFEHL or ISERR will return true or false for all errors except the #NA

 

[hglamy]

Thank you, Peo,

I have done similar things in the past, but in array-formulas
it doesn't seem to work - or I don't know how to make it work
with the braces.

This is my original formula:


{=INDEX($C$4:$D$39;KKLEINSTE(WENN($C$4:$C$39=$G21;ZEILE($C$4:$C$39)-ZEILE($C
$4)+1;ZEILE($C$39)+1);N$20);2)}

Where should I put the "IF"-elements and maintain the array character
of the formular at the same time ?

Kind regards,

H.G. Lamy

 

[Peo Sjoblom]

You might to be able to use

=IF(ERROR.TYPE(INDEX($C$4:$D$39,SMALL(IF($C$4:$C$39=$G21,ROW($C$4:$C$39)-ROW
($C$4)+1,ROW($C$39)+1),N$20),2))=4,NA(),INDEX($C$4:$D$39,SMALL(IF($C$4:$C$39
=$G21,ROW($C$4:$C$39)-ROW($C$4)+1,ROW($C$39)+1),N$20),2))

works for me if I simulate a ref error


error.type in German is FEHLER.TYP, so maybe something like

{=WENN(FEHLER.TYP(INDEX($C$4:$D$39;KKLEINSTE(WENN($C$4:$C$39=$G21;ZEILE($C$4
:$C$39)-ZEILE($C$4)+1;ZEILE($C$39)+1);N$20);2)=4;NV(),INDEX($C$4:$D$39;KKLEI
NSTE(WENN($C$4:$C$39=$G21;ZEILE($C$4:$C$39)-ZEILE($C$4)+1;ZEILE($C$39)+1);N$
20);2))}

array entered

error.type 4 is #REF.

 

[hglamy]

Peo,

thank you for your effort and quick response.

For some reason, with FEHLER.TYP=4, the formula
will not work, but after I modified your suggestion to
the simpler ISTFEHLER version, it finally did the trick.

Thank you again !

Kind regards,

H.G. Lamy