• 470K Members
• 5,142 Online
• 568K 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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies