Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Jun 11, 2019

help with IF statement...

I have a laboratory instrument which spits out mostly numbers, but occasionally a phrase, such as "N/A" or "No Peak" or "<0". However, I needed a column in excel that only contained numbers...so I ma...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 11, 2019

gms4b , you may wrap your formula with IFERROR

=IFERROR(<formula>,0)

 

gms4b's avatar
gms4b
Brass Contributor
Jun 11, 2019

SergeiBaklan 

 

AhHa! So I simply did this and it worked to take out the #DIV/0! statement and turn it to a 0. The remainder of the formula still converts the other statements to 0. 

 

I'm not sure what's going on with the other formula but its not really working right. I think it was giving 0's for everything...including normal numbers. 

 

Thanks for your help!!

 

Greg

 

    • gms4b's avatar
      gms4b
      Brass Contributor
      Jun 11, 2019

      SergeiBaklan 

       

      Thanks Sergei...I will keep playing with it. If I use your formula and put in the direct cell then things do work correctly. When I substitute in the INDIRECT portion instead of the direct cell name then it breaks and just gives me 0. 

       

      I'll play with it again tomorrow....time to pick kids up from school.

       

      Thanks,


      Greg

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 11, 2019

        gms4b 

         

        Greg, I missed what INDIRECT() returns range, not the cell. If so simply wrap the formula by IFERROR like

        =IFERROR(IF(A1:A12="No Peak",0,IF(A1:A12="N/A",0,IF(A1:A12="< 0",0,A1:A12))),0)

        to optimize it it desirably to know more details of how your data is structured. It looks like you return the value from background sheet into the current row by row, from row 2 to row 3, from 3 to 3, etc and column by column. But I'm not sure.