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 made this formula to convert any of these statement to "0" in the neighboring column:

 

=IF(INDIRECT(background!$B$3 & "2:"&background!$B$3 & "245")="No Peak",0,IF(INDIRECT(background!$B$3 & "2:" & background!$B$3 & "245")="N/A",0,IF(INDIRECT(background!$B$3 & "2:"&background!$B$3 & "245")="< 0",0,INDIRECT(background!$B$3 & "2:"&background!$B$3 & "245"))))

 

(the INDIRECT portion cobbles together a column letter and a number from a phrase it searches for on another tab. I don't think that's relevant here,...just the IF statement).

 

And it works fine, But, the instrument also spits out the phrase "#DIV/0!" and Excel doesn't know what to do with it....it just provides the phrase again in the formula column. Any ideas how I can get this phrase to convert to "0" properly?

excel
Formulas and Functions

8 Replies

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

    gms4b 

    Perhaps entire formula

    =IFERROR( IF(SUMPRODUCT(--(INDIRECT(background!$B$3 & "2:"&background!$B$3 & "245")={"No Peak","N/A","< 0"})),0,INDIRECT(background!$B$3 & "2:"&background!$B$3 & "245")),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