Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Jun 24, 2019

ISNUMBER with an IF statement help?

 

Basically, I need for there to be 2 ISNUMBER statements fulfilled before doing a calculation....if not, then leave the cell blank.

 

Below so far, I have: 

=IF(ISNUMBER($AQ$13),AQ4/$AQ$13*100,"")

and this works fine for AR4 and AR5, but gives #DIV/0! for AR6 through AR9 and looks messy. I want a blank.

 

So, I need: 

 

ISNUMBER($AQ$13) and ISNUMBER(AQ4) then AQ4/$AQ$13*100 if not then ""

 

Thanks,


Greg

 

excel
Formulas and Functions

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 24, 2019

    gms4b 

    A slight variation on the previous solution using multi-cell array formula or a dynamic array.

    = IF( Theoretical * QCLow, 100 * QCLow / Theoretical, "" )

    and commit with CSE or allow to spill.  Within an array formula AND will take in the entire array whereas '*' will work term by term.  If the non-numeric values are simple blanks then a test for non-zero will work as well as ISNUMBER.

    If you then remove the zeros with a custom number format, the formula in AR4 reduces to

    = IF( Theoretical, 100 * QCLow / Theoretical, "" )

    and the number format is "0.0; ; ;"

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jun 24, 2019

    gms4b 

     

    coming back to your original question: you can combine two conditions with the AND function

     

    ISNUMBER($AQ$13) and ISNUMBER(AQ4) will be in Excel:

     

    AND(ISNUMBER($AQ$13), ISNUMBER(AQ4))

     

    and the whole formula will be

     

    =IF(AND(ISNUMBER($AQ$13), ISNUMBER(AQ4)),AQ4/$AQ$13*100,"")

Resources