Jun 24 2019 09:50 AM
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
Jun 24 2019 09:59 AM
Jun 24 2019 10:02 AM
Jun 24 2019 02:42 PM
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,"")
Jun 24 2019 04:09 PM
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; ; ;"