ISNUMBER with an IF statement help?

Brass Contributor

 

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

 

image.png

6 Replies

@gms4b 

 

Perhaps

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

@Sergei Baklan 

 

Duh. Hadn't considered that. Easy then.

 

Thanks,


Greg

 

 

@gms4b 

Greg, glad to help

@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,"")

@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; ; ;"

Thanks!