Home

ISNUMBER with an IF statement help?

%3CLINGO-SUB%20id%3D%22lingo-sub-717664%22%20slang%3D%22en-US%22%3EISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717664%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20I%20need%20for%20there%20to%20be%202%20ISNUMBER%20statements%20fulfilled%20before%20doing%20a%20calculation....if%20not%2C%20then%20leave%20the%20cell%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20so%20far%2C%20I%20have%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ISNUMBER(%24AQ%2413)%2CAQ4%2F%24AQ%2413*100%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20this%20works%20fine%20for%20AR4%20and%20AR5%2C%20but%20gives%20%23DIV%2F0!%20for%20AR6%20through%20AR9%20and%20looks%20messy.%20I%20want%20a%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20need%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EISNUMBER(%24AQ%2413)%20%3CEM%3E%3CSTRONG%3Eand%3C%2FSTRONG%3E%3C%2FEM%3E%20ISNUMBER(AQ4)%20then%26nbsp%3BAQ4%2F%24AQ%2413*100%20if%20not%20then%20%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20337px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120450i29D59411A89BD34C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-717664%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717691%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(AQ4%2F%24AQ%2413*100%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717707%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDuh.%20Hadn't%20considered%20that.%20Easy%20then.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717719%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreg%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718516%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecoming%20back%20to%20your%20original%20question%3A%20you%20can%20combine%20two%20conditions%20with%20the%20AND%20function%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EISNUMBER(%24AQ%2413)%20%3CSTRONG%3E%3CEM%3Eand%3C%2FEM%3E%3C%2FSTRONG%3E%20ISNUMBER(AQ4)%20will%20be%20in%20Excel%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAND(ISNUMBER(%24AQ%2413)%2C%26nbsp%3BISNUMBER(AQ4))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20the%20whole%20formula%20will%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-family%3A%20'Calibri'%2Csans-serif%3B%22%3E%3DIF(AND(ISNUMBER(%24AQ%2413)%2C%26nbsp%3BISNUMBER(AQ4))%2CAQ4%2F%24AQ%2413*100%2C%22%22)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718574%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20slight%20variation%20on%20the%20previous%20solution%20using%20multi-cell%20array%20formula%20or%20a%20dynamic%20array.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20IF(%20Theoretical%20*%20QCLow%2C%20100%20*%20QCLow%20%2F%20Theoretical%2C%20%22%22%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eand%20commit%20with%20CSE%20or%20allow%20to%20spill.%26nbsp%3B%20Within%20an%20array%20formula%20AND%20will%20take%20in%20the%20entire%20array%20whereas%20'*'%20will%20work%20term%20by%20term.%26nbsp%3B%20If%20the%20non-numeric%20values%20are%20simple%20blanks%20then%20a%20test%20for%20non-zero%20will%20work%20as%20well%20as%20ISNUMBER.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIf%20you%20then%20remove%20the%20zeros%20with%20a%20custom%20number%20format%2C%20the%20formula%20in%20AR4%20reduces%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E%3CFONT%3E%3D%20IF(%20Theoretical%2C%20100%20*%20QCLow%20%2F%20Theoretical%2C%20%22%22%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%3Eand%20the%20number%20format%20is%20%22%3CSTRONG%3E0.0%3B%20%3B%20%3B%3C%2FSTRONG%3E%22%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725500%22%20slang%3D%22en-US%22%3ERe%3A%20ISNUMBER%20with%20an%20IF%20statement%20help%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725500%22%20slang%3D%22en-US%22%3EThanks!%3C%2FLINGO-BODY%3E
Highlighted
gms4b
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!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies