• 414K Members
• 6,768 Online
• 480K Conversations

Contributor

# 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

6 Replies

# Re: ISNUMBER with an IF statement help?

Perhaps

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

# Re: ISNUMBER with an IF statement help?

Duh. Hadn't considered that. Easy then.

Thanks,

Greg

Highlighted

# Re: ISNUMBER with an IF statement help?

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

# Re: ISNUMBER with an IF statement help?

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

# Re: ISNUMBER with an IF statement help?

Thanks!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies