Remove Zeros from Data

[Tripp]

Hello,

I have a formula which picks up certain data from a Pivot table. The formula has been working fine until I found some Zeros in the data which are being brought through and disrupting my charts down the line. The formula is as follows:

=IFERROR(IF(GETPIVOTDATA("Result",PivotTable!$A$4,"Sample Date",$A580,"Determinand Name",C$1)="",NA(),GETPIVOTDATA("Result",PivotTable!$A$4,"Sample Date",$A580,"Determinand Name",C$1)),NA())

I tried adding an OR function but it just makes the cell = #N/A. I know I can use IFS but that would make my formula very long with lots of repeating, surely there must be a better way?

How can I add Zero as well as blank detection into this formula?

Regards,

Tripp

 

[NARAYANK991]

Hi ,

See this post :

https://chandoo.org/forum/threads/alternative-to-if-isnumber-a1-a1.36357/#post-218079

You don't really need the IF statement within the IFERROR.

Narayan

 

[Tripp]

Hi Narayan,

Thanks so much, I have adapted the formula to the following, much sexier:

=IFERROR(1/(1/GETPIVOTDATA("Result",PivotTable!$A$4,"Sample Date",$A581,"Determinand Name",C$1)),NA())

Cheers!