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

Highlighted

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies