Home

Removing "#NUM!" Error

%3CLINGO-SUB%20id%3D%22lingo-sub-780021%22%20slang%3D%22en-US%22%3ERemoving%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780021%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20understand%20what%20I%20am%20doing%20wrong.%20%26nbsp%3BCan%20someone%20please%20help!%20%26nbsp%3BWorking%20on%20office%20365%2C%20and%20making%20a%20table%20to%20consolidate%20data%2C%20and%20remove%20spaces.%20%26nbsp%3BMy%20formula%20works%20(as%20far%20as%20data)%2C%20and%20I%20watched%20a%20guy%20on%20youtube%20do%20the%20same%20exact%20formula.%20%26nbsp%3BHis%20worked...%20mine%2C%20not%20so%20well!%20%26nbsp%3B%20Why%20will%20excel%20not%20see%20the%20%2C%22%22%2C%20and%20make%20it%20blank%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20956px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125006iD4EDDBD8806D3891%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image001.png%22%20title%3D%22image001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-780021%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780036%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780036%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20wrap%20your%20INDEX%20formula%20with%20IFERROR%2C%20i.e.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIFERROR(INDEX(...)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781301%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781301%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%3EThank%20you%20so%20much%20for%20responding.%20%26nbsp%3BWould%20I%20put%20my%20code%20in%20the%20%22...%22%20part%20of%20your%20example%3F%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781595%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(%24E%242%3A%24E396%2C..%20till%20end%20of%20INDEX%20pat)%2C%22%22).%20Remove%20IF%20with%20conditions%20at%20all.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781673%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781673%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%3EOMG!!!%20Thank%20you%20so%20much!!!%20%26nbsp%3B%20That%20worked.%20%26nbsp%3BI%20was%20going%20crazy%20trying%20to%20figure%20that%20out.%20%26nbsp%3BWatching%20youtube%20videos%2C%20etc.%20%26nbsp%3B%20Hard%20to%20try%20and%20google%20an%20issue%20of%20coding%20when%20you%20can't%20explain%20what%20it%20is%20doing%20wrong!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20soooo%20much%20and%20have%20an%20awesome%20day!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781716%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781716%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781794%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20%26amp%3Bquot%3B%23NUM!%26amp%3Bquot%3B%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20that%20the%20problem%20is%20on%20your%20first%20argument%20of%20the%20IF%20function...%20It%20states%20that%20the%20formula%20should%20work%20only%20for%20the%20amount%20of%20rows%20that%20have%20data%20in%20it.%20It's%20a%20nice%20approach%2C%20but%20if%20the%20data%20you're%20looking%20at%20are%20not%20hardcoded%20data%20(if%20they%20come%20form%20formulas)%20the%20COUNTA%20function%20still%20counts%20the%20%22empty%22%20cells.%20So%20instead%20of%20stopping%20the%20formula%20where%20the%20cells%20are%20empty%2C%20he%20repeats%20it%20for%20as%20many%20rows%20as%20you%20have%20applied%20your%20other%20formulas%20to.%20Indeed%2C%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3ESergei%20Baklan%3C%2FA%3E%26nbsp%3Bsaid%2C%20it's%20better%20just%20to%20wrap%20it%20with%20and%20IFERROR.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Budman36
Contributor

I don't understand what I am doing wrong.  Can someone please help!  Working on office 365, and making a table to consolidate data, and remove spaces.  My formula works (as far as data), and I watched a guy on youtube do the same exact formula.  His worked... mine, not so well!   Why will excel not see the ,"", and make it blank?

image001.png

6 Replies

@Budman36 

Better wrap your INDEX formula with IFERROR, i.e.

=IFERROR(INDEX(...),"")

 

@Sergei Baklan 

 

Thank you so much for responding.  Would I put my code in the "..." part of your example?  

 

B.

@Budman36 

=IFERROR(INDEX($E$2:$E396,.. till end of INDEX pat),""). Remove IF with conditions at all.

@Sergei Baklan 

 

OMG!!! Thank you so much!!!   That worked.  I was going crazy trying to figure that out.  Watching youtube videos, etc.   Hard to try and google an issue of coding when you can't explain what it is doing wrong!

 

thank you soooo much and have an awesome day! 

 

B. 

Highlighted

@Budman36 , you are welcome, glad to help

@Budman36 

I think that the problem is on your first argument of the IF function... It states that the formula should work only for the amount of rows that have data in it. It's a nice approach, but if the data you're looking at are not hardcoded data (if they come form formulas) the COUNTA function still counts the "empty" cells. So instead of stopping the formula where the cells are empty, he repeats it for as many rows as you have applied your other formulas to. Indeed, as Sergei Baklan said, it's better just to wrap it with and IFERROR.

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